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?

  • 1
    Try 4326 instead?
    – mkennedy
    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/…
    – Mapperz
    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.
    – BuschnicK
    Dec 2, 2010 at 22:13

1 Answer 1


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:

  • 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...
    – BuschnicK
    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/…
    – underdark
    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.
    – BuschnicK
    Dec 2, 2010 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.