2) PostGREsql is installed, and the\u00a0PostGIS extension is enabled.<\/strong> If you need to install PostGREsql and PostGIS, check out the tutorial at Boston GIS demonstrating how to acquire and install PostGREsql with PostGIS<\/a>.\u00a0It won’t hurt to reveiw their entire tutorial, but I deviate from their approach once installation is complete (look for\u00a0their sub-heading, Loading GIS Data Into the Database<\/em>).<\/p>\n 3) You have some geodata<\/strong>. I think the typical reader will have their own shapefile, pesonal geodatabase, or otherwise, but if you need something to follow along, here’s\u00a0a US States shapefile projected to NAD83 Albers Equal Area Conic:<\/p>\n http:\/\/www.cartometric.com\/blog\/wp-content\/uploads\/data\/usstates_nad83_aeac.zip<\/a><\/p>\n To push shapefile data into your geodatabase, you can run an ogr2ogr script like this:<\/p>\n ogr2ogr -f “PostGreSQL” PG:”host=127.0.0.1 user=youruser dbname=yourdb password=yourpass” “E:\\4_GIS\\01_tutorials\\usstates_nad83_aeac\\usstates_albers.shp” -nln usstates -nlt geometry<\/p><\/blockquote>\n For deeper reading on ogr2ogr utility flags (like -nln and nlt), check out the usage notes for ogr2ogr<\/a>. Also, it may be worth you while to peruse the OGR\u00a0PostgreSQL driver page<\/a>, as well as the Advanced Driver Information page<\/a>. In the meantime, here are a few quick notes regarding my script:<\/p>\n -f “PostGreSQL” PG:”host=127.0.0.1 user=youruser dbname=yourdb password=yourpass”<\/strong>\u00a0 This tells OGR you’re exporting to PostGreSQL with the following connection string. Notice that my connection string is wrapped in double-quotes (“).<\/p>\n “E:\\4_GIS\\01_tutorials\\usstates_nad83_aeac\\usstates_albers.shp”<\/strong>\u00a0\u00a0 This is the path to my shapefile \u00a0input data. Once again, I wrapped this value in double-quotes (“). I do this to prevent the console from introducing linebreaks into the argument value\u00a0and confusing the parser.<\/p>\n -nln usstates<\/strong>\u00a0 The -nln flag means “rename the table on export”. In other words, my PostGREsql db will\u00a0get a new table named usstates<\/em>, and not one named usstates_albers<\/em>.<\/p>\n -nlt geometry<\/strong>\u00a0 This one’s\u00a0particularly important for polygon data. It tells OGR “accept any geometry you encounter and store it in the feature’s geometry column”. Oftentimes, a polygon dataset will have polygons and multipolygons in the same table. For example here’s a narrow column of\u00a0Well Known Text (WKT)\u00a0geometries from the albers shapefile so you can see what I mean:<\/p>\n WKT;STATE_NAME;STATE_FIPS;STATE_ABBR….. If you run the ogr2ogr script\u00a0noted above without -nlt geometry, you’ll get an error like this:<\/p>\n ERROR 1: Terminating translation prematurely after failed<\/em> By default, OGR refuses to mix geometry types in a table, so -nlt geometry allows you to\u00a0duck that requirement and store both Polygon and Multipolygon features in the same table. You could optionally instruct OGR to “explode” Multipolygons into individual Polygons using the -explodecollections flag, as depicted in\u00a0the following screenshot, but I don’t recommending that solution for the intended use case. For example, if a map user clicks on Michigan’s Upper Penenssula, I want the whole state to be selected, not just the UP. I’m not saying you can’t make that happen after exploding multifeatures; rather, it’s just not the approach I favor.<\/p>\n Without -nlt geometry, ogr2ogr will throw an error if it attempts to export polygons and multipolygons into the same table. Alternatively, you can use the flag -explodecollections (not recommended in this case) to translate your multipolygons into several polygons.<\/p><\/div>\n Assuming you used the script like the one I initially provided, you should be able to open pgAdmin III (the PostGREsql admin GUI that insalls with the database) and see your new usstates<\/em> table:<\/p>\n With pgAdmin III open, expand the Tools menu and launch the Query tool. You’ll use the Query tool\u00a0and the following SQL instructions to prep your data for production. I’ll start by listing all the queries together, then I’ll provide some deeper explaination in the text that follows.<\/p>\n SELECT SRID(wkb_geometry) FROM usstates; Basically, the steps emphasized in blue do the actual work, while the steps in black are more for sanity checks. Steps\u00a04 and\u00a05 perform the geometry transformation, and the last two steps do some house-cleaning and\u00a0table optimization. Now I’ll\u00a0provide a\u00a0one-by-one discussion of\u00a0each step.<\/p>\n 1) SELECT SRID(wkb_geometry) FROM usstates;<\/strong><\/p>\n Here we’re getting\u00a0the SRID for the features in this layer (which is 900925 on my system). By default, OGR will store\u00a0feature geometries in a field called wkb_geometry<\/em>. Also, your PostGIS installation includes a table named spatial_ref_sys<\/em> that stores coordinate system definitions necessary\u00a0for the database to remain “spatially aware”\u00a0of your new table as well as the other spatial datasets the system is managing. Consider this, if you want to select points from one layer that fall inside polygons from another layer, PostGIS needs to understand the coordinate systems for both datasets so that it can align\u00a0their features\u00a0for analysis. So when we run the SRID() function on the table’s geometry field, wkb_geometry<\/em>, PostGREsql will return the unique identifier for the coordinate system used to define\u00a0the features in our table.<\/p>\n 2) SELECT * FROM spatial_ref_sys WHERE srid = 900925;<\/strong><\/p>\n In this step we answer the question, “Does the SRS\u00a0established at\u00a0import makes sense for the data?” This statement queries\u00a0the PostGIS spatial_ref_sys<\/em> table for the coordinate system whose ID is referenced in the previous step. Check the srText<\/em> field for a readable version of the coordinate system. Mine\u00a0begins with “PROJCS[“North_America_Albers_Equal_Area_Coni..”<\/em>\u00a0 That’s what I expected, and that’s a good thing.<\/p>\n 3) SELECT st_asText(wkb_geometry) FROM usstates;<\/strong><\/p>\n Now I like to do a quick query to see the WKT for some of my features. The geometry field wkb_geometry<\/em>\u00a0was created by ogr2ogr when it imported the shapefile into PostGREsql. If you don’t like this name, you can use the creation option -lco GEOMETRY_NAME=geom<\/em><\/strong>\u00a0in your ogr2ogr import\u00a0script to set the name of the geometry field at import time. As shown in the image, the WKT for my features looks like I would expect.<\/p>\n Querying feature geometries using the ST_asText() function on the geometry column, wkb_geometry.<\/p><\/div>\n 4) ALTER TABLE usstates ADD COLUMN wgs84geom GEOMETRY;<\/strong><\/p>\n This instruction adds\u00a0a new column to the table, which\u00a0I’ll use to\u00a0store\u00a0the feature geometries for my US States in\u00a0geodetic coordinates. The column will be named wgs84geom<\/em>\u00a0and will expect data of\u00a0type GEOMETRY.\u00a0In other words, this field will store\u00a0a permanent “cast” of our feature geometries\u00a0in the WGS84 coordinate system, which is very popular due to its use by the famous Global Positioning System (GPS).<\/p>\n Note: GIS coordinate systems are complex beasts, and it’s easy to get lost in their particulars. Nevertheless, one distinction is very important, and that’s the\u00a0difference between projected systems and geodetic systems. Projected systems are two-dimensional. —these are\u00a0the X\/Y grids you used for trigonometry exercises in High School. On the other hand, geodetic systems\u00a0define\u00a0coordinate geometries within a\u00a0three-dimensional, spherical space.<\/p>\n Both systems are roses by many names. For instance, projected systems may be called “Cartesian” or “Geometric”. And Geodetic systems may be called “Sexigesimal” or “Geographic”. The PostGIS community may more often refer to features as being geometry, or geography data and mean projected\u00a0vs. geodetic coordinates, respectively. So\u00a0if you run across language like this, realize people intend for geometry\u00a0<\/em>to imply\u00a0X and Y coordinates in a cartesian space, and for geography\u00a0<\/em>to mean familiar\u00a0longitude and latitude coordinates.<\/p>\n In this image, state boundaries are drawn in Albers Equal Area Conic, which is a projected coordinate system.<\/p><\/div>\n <\/p>\n Here the state boundaries are "represented" in NAD83 geographic coordinates. NAD83 geographic (EPSG 4269), is similar, if not nearly identical, to WGS84. As you can see, geodetic systems are difficult to represent in 2D space; as such, unprojected maps of large areas tend to look "suspect".<\/p><\/div><\/blockquote>\n 5) UPDATE usstates SET wgs84geom = st_Transform(wkb_geometry, 4326);\u00a0<\/strong><\/p>\n With\u00a0the new column ready to go,\u00a0you can now\u00a0wield an UPDATE statement and the st_Transform() function to\u00a0translate feature geometries from their projected coordinates\u00a0to their\u00a0geographic WGS84 coordinates.\u00a0The st_Transform() function expects two arguments,\u00a0the source geometry field to transform, and\u00a0the EPSG code for the output coordinate system. WGS84\u00a0is a fundamentally-popular coordinate system, and it’s EPSG code of 4326 is easy to find. If you do not know the EPSG code for your preferred coordinate system, head over to http:\/\/spatialreference.org\/<\/a>\u00a0and do some quick research.\u00a0<\/p>\n Note: We could optionally perform\u00a0any coordinate system transformations in our queries by calling st_Transform on\u00a0the geometry field right in the query. However, by\u00a0casting our feature geometries in advance, we remove calculation overhead and get a subtle efficiency gain. This can particularly improve response times for spatial queries.<\/p><\/blockquote>\n 6) SELECT SRID(wgs84geom) FROM usstates;<\/strong><\/p>\n Like\u00a0the second step,\u00a0this query is only intended to\u00a0confirm whether the SRID established in\u00a0the previous step\u00a0makes sense for the data. It should return 4326.<\/p>\n 7) SELECT st_asText(wgs84geom) FROM usstates;<\/strong><\/p>\n Also like the third step, here I’m querying the feature geometrires as WKT to\u00a0make sure they’re defined by longitude\/latitude coordinate pairs.<\/p>\nLoading Geodata into PostGREsql \/ PostGIS<\/h2>\n
\n“MULTIPOLYGON<\/strong> (((-1827806.2165497246 1227…..
\n“POLYGON<\/strong> ((-1148108.0134327484 649421.311…..
\n“MULTIPOLYGON<\/strong> (((1949555.0881544715 75264…..
\n“POLYGON<\/strong> ((-199237.01920416657 704421.540…..
\n“POLYGON<\/strong> ((-519870.38897088548 372373.616…..<\/p>\n
\ntranslation of layer usstates_albers (use -skipfailures to skip errors)<\/em><\/p>\n![\"\"](\"http:\/\/cartometric.com\/blog\/wp-content\/uploads\/2011\/11\/ogr-explodecollections-300x119.png\" "\\"Using")
<\/a>Post-Processing your Geodata with SQL Instructions<\/h2>\n
\nSELECT * FROM spatial_ref_sys WHERE srid = 900925;
\nSELECT st_asText(wkb_geometry) FROM usstates;
\nALTER TABLE usstates ADD COLUMN wgs84geom GEOMETRY;
\n<\/span>UPDATE usstates SET wgs84geom = st_Transform(wkb_geometry, 4326);\u00a0
\n<\/span>SELECT SRID(wgs84geom) FROM usstates;
\nSELECT st_asText(wgs84geom) FROM usstates;
\nVACUUM usstates;<\/span>
\nCREATE INDEX usstates_wgs84_idx ON usstates USING GIST(wgs84geom);<\/span><\/p>\n![\"\"](\"http:\/\/cartometric.com\/blog\/wp-content\/uploads\/2011\/11\/pgAdminIII-query-tool-select-st_asText-300x209.png\" "\\"pgAdminIII-query-tool-select-st_asText\\"")
<\/a>![\"\"](\"http:\/\/cartometric.com\/blog\/wp-content\/uploads\/2011\/11\/usstates_albers_image-300x224.png\" "\\"US")
<\/a>![\"\"](\"http:\/\/cartometric.com\/blog\/wp-content\/uploads\/2011\/11\/usstates_nad83_image-300x160.png\" "\\"US")
<\/a>
![\"\"](\"http:\/\/cartometric.com\/blog\/wp-content\/uploads\/2011\/11\/pgAdminIII-with-WKT-in-lon-lat-300x209.png\" "\\"pgAdminIII-with-WKT-in-lon-lat\\"")
<\/a>