[Gdal-dev] Basic Pg Question

[Brian Hamlin]

On Sep 27, 2007, at 5:28 PM, Brian Hamlin wrote:

>>
>>>
>>> 1) install postgis in your pg database if you have not done so 
>>> already.
>>> 2) Create a point geometry column in your table using 
>>> AddGeometryColumn()
>>> 3) Do an update with the postgis MakePoint() function (ie UPDATE 
>>> table
>>> SET geom = SETSRID(MakePoint(lon, lat),4326) )
>>>
>>> The SETSRID(.., 4326) function is used to define the resulting
>>> geometry as geographic coordinates with a WGS84 datum. If your
>>> coordinate system is different, you'll need to use the proper spatial
>>> reference id code.
>>>
>>>
>>
>>  thank you for the tips..
>> I am a little stuck.. AddGeometryColumn() wants an SRID as "an 
>> integer value reference to an entry in the SPATIAL_REF_SYS table", 
>> but the SPATIAL_REF_SYS table appears to be empty after creating a 
>> new spatially enabled DB from scratch.
>>
>>
>  ok, I found another paragraph in the docs that provided enough clues 
> to move forward... I just use 4326 as SRID and the right things 
> happen..  I wrote the insert script using Perl and DBI, so its pretty 
> easy to change and make experiments..
>
> unfortunately, the import is WAY slower with the Update table in the 
> loop, and it essentially crashed after only 10% of the rows done!  
> ERROR: shared buffer has table corrupted    :(
>
>

I re-ran the script, it took almost 20 minutes, then the server process 
closed the connection (on localhost), and less than 20% of the rows are 
imported!  this is not working... The UPDATE with SETSRID(MakePoint()) 
is way too slow

I think I should write a script that generates the WKT as a field, and 
import that.. Pointers welcome
    -Brian