Importing Shapefile into PostgreSQL from an ASP.NET web application

I’ve noticed people looking for an answer to this quite often so I’m publishing the steps to  achieve this. Hopefully this will help organizations that want their GIS users to upload GIS data into the spatial database.

NOTE: If you want to simply import a large amount of shapefiles to your PostGIS database, you can simply write a BAT file that uses shp2pgsql and call that from an executable.
This article is to solve a very specific problem…importing a Shapefile into a PostGIS database, through a web application.

To be able to import the data using this process, you must have at least these 4 components of the shapefile:
SHP, SHX, DBF & PRJ.

Step 1) Upload the files using the traditional server control to upload the files
<FileUpload ID=”FUpl1″ runat=”server” />
Make sure you check for the validity of the files

Step 2) Get the projection of the shapefile from the PRJ  file (which is a text file with one line in it, usually). I’ll leave this to you to figure this part out….shouldn’t be too hard.
Get the SRID for the projection. If this is a custom projection, make sure the SRID for this custom projection is defined on the spatial database.

Step 3) Upload your files to your server using
FUpl1.PostedFile.SaveAs(SERVER_SIDE_PATH_TO_THE_FILE);

Step 4) Convert your shapefile into SQL code using the tools provided with PostGIS.
Right….easier said than done. Using Command.exe to run an executable in a web application directly isn’t really a bright idea, is it!
Here’s how you solve that dilemma.
Write a WCF service to do what you wanted to do here in your web application. Then install that WCF service and expose it to the web application. That way you can outsource the “dangerous” work to a semi web, semi windows application that will provide you the stability and security of a windows service but expose the same to the web application as a web service! That said…I leave it up to you to securely expose the WCF service to the web application ONLY.
Here’s a clue as to what can be done inside the WCF service to import the data:
C# Code:

        public string ConvertToSql(string ShapeFileName, string srid)
        {
            string result = "";
            string sqlfilename = ShapeFileName.ToLower().Replace(".shp",".sql");
            string pathToSqlFolder = WHERE_EVER_SQL_FILES_ARE_TO_BE_STORED;

            if (!pathToSqlFolder.EndsWith("\\"))
                pathToSqlFolder += "\\";
            if (!Directory.Exists(pathToSqlFolder))
            {
                return "ERROR: SQL files folder " + pathToSqlFolder + " does not exist.";
            }

            string SqlFilePath = pathToSqlFolder + sqlfilename;
            if (File.Exists(SqlFilePath))
               File.Delete(SqlFilePath);

            string pathToShapefilesFolder = WHERE_EVER_SHAPEFILE_IS_STORED;
            if (!pathToShapefilesFolder.EndsWith("\\"))
                pathToShapefilesFolder += "\\";
            string ShapefilePath = "";
            ShapefilePath = pathToShapefilesFolder + ShapeFileName;

            string args = @" -s {0} -c {1} {2} > {3}";
            //fill in the arguments & the rest
            args = String.Format(args, srid, ShapefilePath.Replace(".shp", ""), sqlfilename.Replace(".sql", ""), SqlFilePath);

            try
            {
                string pathToImporter = System.Configuration.ConfigurationSettings.AppSettings["PathToImportTool"];
                string strConverter = "shp2pgsql.exe ";
                strConverter = pathToImporter.EndsWith(@"\") ? pathToImporter + strConverter : pathToImporter + @"\" + strConverter;
                strConverter = strConverter + args;
                Process objProcess = new Process();
                ProcessStartInfo objPSI = new ProcessStartInfo(@"c:\windows\system32\cmd.exe", "/C " + strConverter);

                objPSI.UseShellExecute = true;
                objPSI.WindowStyle = ProcessWindowStyle.Normal;
                bool blnIsReady = false;

                objProcess.StartInfo = objPSI;
                objProcess.Start();
                objProcess.WaitForExit();
                blnIsReady = objProcess.HasExited;

                if (blnIsReady)
                    result = "SUCCESS: " + sqlfilename;
                else
                    result = "ERROR: Importing of shapefile did not succeed. No problem details available.";
            }
            catch (Exception ex)
            {
                result = "ERROR: Exception while importing shapefile to SQL."+ex.ToString();
            }
            return result;
        }

Alright, now that the SQL file has been created, we can proceed to the next step.

Step 5) Read the content of the SQL file, and execute it. Keep  in mind that the data will be imported into the spatial table in the same projection that the shapefile was in. If you want to reproject it, I suggest doing that by first importing it into a temporary table, reprojecting the data inserting it into the desired final destination.

There…you now have a Spatial table from your shapefile. ENJOY!!!

P.S:  Please feel free to propose any improvements, or if you were able to adapt this to solve a problem you were facing. I’d love to hear about that 🙂

Advertisements

SharpMap.NET – Post 1

I’ve been fiddling around with SharpMap and PostGIS so that I could display some GIS Data in a web application. I searched around and found very meager resources out w.r.t SharpMap. So I decided to post my progress as I programmed my way around the component.

For my first post, I just displayed some GIS Data for the state of Louisiana.

ASPX Code:

<%@ Page Title="" Language="C#" MasterPageFile="~/Geo.Master" AutoEventWireup="true" CodeBehind="map.aspx.cs" Inherits="SharpMapOne.map" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
        <div style="border: 1px solid #000000; padding: 10px;">
            <asp:Image ID="Image1" runat="server" />
        </div>
</asp:Content>

C# Code:

        protected void Page_Load(object sender, EventArgs e)
        {
            //Get the map object
            SharpMap.Map mp = new SharpMap.Map();
            mp.MinimumZoom = 100;
            mp.BackColor = System.Drawing.Color.Transparent;

            //create the vector layer
            SharpMap.Layers.VectorLayer vl = new SharpMap.Layers.VectorLayer("LA_HOUSE");
            vl.DataSource = new SharpMap.Data.Providers.PostGIS(ConfigurationSettings.AppSettings["GISConnString"], "la_uscon", "17796");
            vl.Style.Outline = System.Drawing.Pens.Red;
            vl.Style.EnableOutline = true;
            vl.Style.Line = new System.Drawing.Pen(System.Drawing.Color.Aquamarine);
            vl.Style.Fill = new SolidBrush(Color.Aquamarine);
            mp.Layers.Add(vl);

            //create the label acetate layer
            SharpMap.Layers.LabelLayer layASLabel = new SharpMap.Layers.LabelLayer("LA_HOUSE_DISTS");
            layASLabel.DataSource = vl.DataSource;
            layASLabel.LabelColumn = "cd111fp";
            layASLabel.Style.Font = new Font("Arial", 6, FontStyle.Bold);
            layASLabel.Style = new SharpMap.Styles.LabelStyle();
            layASLabel.Style.ForeColor = Color.Black;
            layASLabel.Style.Offset = new PointF(10, 0);
            layASLabel.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
            layASLabel.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
            layASLabel.Style.CollisionDetection = true;
            layASLabel.Style.CollisionBuffer = new SizeF(5, 5);
            layASLabel.MultipartGeometryBehaviour = SharpMap.Layers.LabelLayer.MultipartGeometryBehaviourEnum.Largest;
            mp.Layers.Add(layASLabel);

            mp.ZoomToExtents();
            System.Drawing.Image img = mp.GetMap();
            int ht = img.Height;
            int wd = img.Width;
            double ratio =  Convert.ToDouble( wd / ht);
            int new_ht = 400;
            int new_wd = -1;
            new_wd = Convert.ToInt32(new_ht * ratio);
            Image1.Height = new_ht;
            Image1.Width = new_wd;
            string imgID = SharpMap.Web.Caching.InsertIntoCache(5,img);

            Image1.ImageUrl ="mymap.aspx?ID=" + HttpUtility.UrlEncode(imgID);
        }

It results in this map.

Now now…I know it ain’t purdy, but its a map. We’ll pretty it up later and add functionality as well.

NOTE: Dont forget to add the Http Handler in your web.config that will handle the requests to mymap.aspx!
<httpHandlers>
…………
<add verb=”*” path=”mymap.aspx” type=”SharpMap.Web.HttpHandler,SharpMap”/>
</httpHandlers>