[postgis-users] Can't build GIST index on table with > 5, 000, 000 rows

[Stephen Woodbridge]

Chip,

One issue is that you are creating you POINT with (Lat, Long) and you 
should be creating it with (Long, Lat). If you fix that and it works 
make sure you write a bug up on it.

Secondly, in your script you might be able to eliminate the helper 
function and just do something like this:

update gns set the_geom = SetSRID(MakePoint(dd_long, dd_lat), 4326);

Which should create the point for every row in the database without 
using all the string manipulation stuff and should be quite a bit faster.

-Steve W.
  http://imaptools.com/

Chip Masters wrote:
> I downloaded the NGA Geographical Names dataset (from
> http://earth-info.nga.mil/gns/html/cntry_files.html)
> and built a spatial database using the attached
> script, nga.sql. The resulting table has over 5.5
> million rows. Everything went fine until it reached
> the step in my script that builds the GIST index on
> the_geom column:
> 
> 
>>create index gns_the_geom_gist on gns using gist 
>>(the_geom);
> 
> 
> I let this run for around 24 hours before I realized
> that postgres had died without producing any error
> message. Unfortunately, I don't have any further
> debugging information to provide.
> 
> I was wondering if there is a known limitation on the
> size of a GIST index, or if there is another method I
> could use to build the index incrementally.
> 
> I'm running PostgreSQL 8.0.3. Here's my postgis
> version info:
> 
> nationalatlas=# select postgis_full_version();
>                                         
> postgis_full_version                                  
>       
> ------------------------------------------------------------------------------------------------------
>  POSTGIS="1.0.1" GEOS="2.1.2" PROJ="Rel. 4.4.9, 29 Oct
> 2004" USE_STATS DBPROC="0.3.0" RELPROC="0.3.0"
> (1 row)
> 
> Thanks in advance,
> 
> Chip Masters
> 
> 
> 
> 
> 		
> ____________________________________________________
> Start your day with Yahoo! - make it your home page 
> http://www.yahoo.com/r/hs 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users