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

[Chip Masters]

Thanks, Stephen. After fixing my script, it worked
fine. Thanks also to Gregory Williamson, who corrected
my "create index ... using gist ..." syntax.

I will try to reproduce the original error and distill
a use case for filing a bug report.

Chip

--- Stephen Woodbridge <woodbri at swoodbridge.com>
wrote:

> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
>
http://postgis.refractions.net/mailman/listinfo/postgis-users
> 





		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs