7

I have a PostGis database with a multipolygon geography (as opposed to geometry) column. I'd like to fill it with the country border data available from http://thematicmapping.org/downloads/world_borders.php The query I tried is 

insert into "countries" select "un", "iso3", "name", "region", "subregion", ST_GeogFromText( 'POINT(' || "lon"::varchar || ' ' || "lat"::varchar || ')' ), ST_Transform( "the_geom", 4326 ) from "world";

Where "world" contains an import of the raw (geometry) data from the link. However, it complains about unknown input geometry SRID -1. I have tried explicitly setting it to 900913 via ST_SetSrid, but that didn't change anything (and I'm not even sure it's the correct SRID to use, just guessing).

So my question is: How do I get a country border data set into my geography column?

Improve this question
3
  • 1
    Try 4326 instead? Commented Dec 2, 2010 at 1:57
  • geography_columns - One restriction is that it only supports lat/lng WGS84 (SRID:4326) postgis.refractions.net/documentation/manual-svn/… Commented Dec 2, 2010 at 2:22
  • I know - that's why I'd like to transform it to 4326. I'm a total newbie to this spatial database stuff and still have some trouble with the terminology. Commented Dec 2, 2010 at 22:13

1 Answer 1

Reset to default
5

Check geometry_columns table: Does it contain a line for "world" table with SRID = 4326? If not, add/edit it. (The shapefile from thematicmapping.org seems to be in 4326.)

Also, looks like the conversion from geometry to geography is missing:

"the_geom"::geography
Improve this answer
3
  • No, the shp2pgsql tool created the geometry column as: AddGeometryColumn('public','world','the_geom','-1','MULTIPOLYGON',2); That's the reason for my question in the first place - the SRID is -1 which ST_Transform complains about. It also added a constraint that verifies the SRID stays -1 which I had to manually drop before using ST_SetSrid. Ah well, I seem to have some learning to do... Commented Dec 2, 2010 at 22:17
  • You can tell shp2pgsql which SRID to use. -s <SRID> Creates and populates the geometry tables with the specified SRID. Check: postgis.refractions.net/documentation/manual-svn/… Commented Dec 2, 2010 at 22:36
  • Thanks for your help, I think I've got it working now - at least Anchorage is now in the United States where it belongs and no longer in Russia ;-) I'll mark your answer as accepted. Commented Dec 2, 2010 at 22:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.