[postgis-users] GIST INDEX!!

Chris Hermansen chris.hermansen at timberline.ca
Tue Dec 29 15:17:14 PST 2009


Hi César;

Ok so I think part of your problem is that you have the data for each
comuna in a separate table.

Really, there should only be 11 tables in your database, one for each
layer.

Your speed problem comes from opening all the tables to do the queries.
This will be S L O W.

What you need to do is create a database with a table for eg "caminos".
Then you must convert each IGM caminos shape file to a postgis load file
and load each one of those converted shape files INTO THE SAME TABLE.

If you are using ogr2ogr there is an option to append data to an
existing table.  Therefore, for the first comuna, create the table; for
the subsequent 269, append the data to the same table you created.

Do you follow me?

Once you have all the data loaded, then you should create GIST indexes
on your 11 tables' geometry columns.

Si quieres discutirlo en castellano por favor enviame un email privado.

On Tue, 2009-12-29 at 17:26 -0300, César Medina wrote:
> Hi Chris
> 
> 
> when i say "slow", i refer that is no so fast than googleMaps, extreme
> example.!!!
> 
> 
> This is a url with one commune,
>  http://mapas.observatoriourbano.cl/localizacion/map.phtml?config=7101
> 
> 
> In this case, we have 11 layer in database format (Geographic data). 
> if you multiply 11 by 270 comunnes in my country, we have 2970 tables
> aprox, and when i do a zoom over a area, this show the area, but
> "slow".
> 
> 
> 
> 
> for this, I don't do anything relationated with WHERE clause, i don't
> use dificult SQL, just show the layer in the mapfile with: 
> 
> 
> "DATA the_geom from (SELECT the_geom,gid FROM vii_talca_areacomunal)
> AS pas_pred3 USING UNIQUE gid USING SRID=-1"
> 
> 
> as a complement 
> in Hardware, i have two Virtual Machine:
> 1.- Database server, AMD Opteron 64, 2,4 Ghz, 4GB Ram
> 2.- Page server, AMD Opteron 64, 2,4 Ghz, 2GB Ram
> 
> 
> in Software, 
> Linux fedora, php pages, apache, postgresql with postgis, mapserver
> and p.mapper
> 
> 
> I don't know what i have to do, to do it  map more fast !!!!
> 
> 
> thank very much.!!!!
> 
> César http://www.linkedin.com/in/cesarmedinam
> http://foss4gchile.blogspot.com/ mail: ciesareMedina (at) gmail (dot)
> com msn: ciesareMedina (at) hotmail (dot) com skype: ciesare_medina
> 
> 
> 
> 
> 
> > From: chris.hermansen at timberline.ca
> > To: postgis-users at postgis.refractions.net
> > Date: Tue, 29 Dec 2009 11:07:39 -0800
> > Subject: Re: [postgis-users] GIST INDEX!!
> > 
> > César;
> > 
> > You need to be a bit more specific about what you mean by "slow".
> What
> > operations precisely are slow?
> > 
> > Having said that, in general any columns - spatial or otherwise - to
> > which you refer in WHERE clauses in your SELECT statements should be
> > considered for indexing, especially if you use them in join
> conditions.
> > If you have a specific SELECT statement that gives you problems, you
> > might try using an EXPLAIN or ANALYZE together with the statement as
> for
> > example in
> > 
> > http://www.postgresql.org/docs/8.1/static/sql-explain.html
> > 
> > 2700 tables - that is a lot of tables. I have no idea if such a
> large
> > number of tables will slow down query execution, but it seems
> possible.
> > 
> > Do you really need that many tables? For example, if you have all
> the
> > IGM shape files by map sheet for loading, you don't need to create a
> > table for each shape file. Rather you might put all the different
> > "caminos" shape files into one table, and the "comunas" into
> another,
> > and so on. Sorry I'm just guessing at what you might be doing here,
> if
> > I'm wrong please ignore!
> > 
> > La documentación de PostgreSQL es disponible en castellano, por
> ejemplo
> > en el sitio
> > 
> > http://palomo.usach.cl/docshtml/node4.html
> > 
> > On Tue, 2009-12-29 at 15:38 -0300, César Medina wrote:
> > > Dear all
> > > 
> > > 
> > > I am trying to do a tunning to my database, and i have many doubt,
> > > because i think that is very slow (with 3 o 4 users is slow)
> > > 
> > > 
> > > I have 2700 tables aprox. with geometry column in my database, the
> > > street's name, big avenues, regional boundaries, street
> types,comunal
> > > areas, etc. but the tables don't have a index in the geometry
> > > column !!!!!!! - Is recommended do a "GIST" index for each tables
> in
> > > my database? - Could be more fast, if that have a gist index? -
> What
> > > is the benefits ? kings regards, thank you... and happy new year.
> > > 
> > > 
> > > PD: Frecuently i do "vacuum" and "reindex" to my database
> > > César http://www.linkedin.com/in/cesarmedinam
> > > http://foss4gchile.blogspot.com/ mail: ciesareMedina (at) gmail
> (dot)
> > > com msn: ciesareMedina (at) hotmail (dot) com skype:
> ciesare_medina
> > > 
> > > CHILE.
> > > 
> > > 
> > >
> ______________________________________________________________________
> > > Windows Live: Make it easier for your friends to see what you’re
> up to
> > > on Facebook.
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > 
> > -- 
> > Regards,
> > 
> > Chris Hermansen · mailto:chris.hermansen at timberline.ca
> > tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
> > Timberline Natural Resource Group · http://www.timberline.ca
> > 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> ______________________________________________________________________
> Windows Live Hotmail: Your friends can get your Facebook updates,
> right from Hotmail®.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
Regards,

Chris Hermansen    ·    mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5





More information about the postgis-users mailing list