Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
1  
Try 4326 instead? –  mkennedy Dec 2 '10 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 '10 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 '10 at 22:13

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
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 '10 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 '10 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 '10 at 22:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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