[postgis-users] GIST INDEX!!

[César Medina]

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.

[Chris Hermansen]

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

> ______________________________________________________________________

> Windows Live: Make it easier for your friends to see what you’re up to
> on Facebook.

> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Regards,

Chris Hermansen · mailto:chris.h...@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
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[César Medina]

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

---

Windows Live Hotmail: Your friends can get your Facebook updates, right from Hotmail®.

[Ivan Mincik]

> 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 !!!!

Dear Cesar,
using GIST index in geographic database is nearly a must, but still
You can't compare to Google maps, which is using different technology.
p.mapper is fetching data from database and rendering a new map every
time You move in the map. Google maps is using pre-cached map tiles,
which are rendered once and than served from cache as static images
with very good performance. I recommend You to move to OpenLayers and
Tilecache.

Ivan

[Chris Hermansen]

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.

> ______________________________________________________________________

[Ivan Mincik]

One more advice. If Your tables for every area are the same, I will
suggest You having same tables merged in to one. It is better for
management and maybe You can also gain some performance.

[Patricio Cifuentes Ithal]

Cesar,

Hola, primero que bueno encontrar personas de chile aquí. Me parece extraño el tema de la velocidad sobre todo si dices que tienes 4 o 5 usuarios conectados, hay varios pasos a seguir para saber si esta bien el seteo de software como de hardware, en términos generales veo que es un buen hardware sobre todo por el tema de 64bit, que es donde corre mucho mejor el postgres, casi tres a cuatros veces que una maquina de 32, pero medio corto en términos de RAM, pero depende de su tunning, como por ejemplo el share_buffer sus check_point, etc, lo otro son los índices de tus tablas, como regla genero 3 a 4 indices, ya que son por los cuales la mayoría de las veces se generan las búsquedas espaciales, los primeros indices obviamente deben ser por el campo the_geom y por el gid, que deben ser GiST y btree, después en el caso que se hagan búsqueda por algún campo propio de tu geo tabla, como por ejemplo búsqueda por ingeniería social, llamece por ROL o un RUT, a esos también debieces generarles índices,

Volviendo al tema postgres el ideal es que sea sobre la versión 8.3 en adelante, la versión del postgis 1.4, si no no tiene mucho sentido, y obviamente si tu maquina es de 64bit, debes haberle instalado el SO de 64bit y la compilación del postgres o su instalación RPM deben ser de 64 bit ojo con eso por q muxas veces tu instalas un postgres en una maquina 64 y queda la instalación de 32, de andar anda, pero es como si le pusiera ruedas de triciclo a un auto (guardando las proporciones). Ojala te sirva la ayuda y si necesitas mas pistas o tips no hay problema en seguir apoyando, también puedes revisar algunos de los sistemas que desarrollamos con open free gis, en www.idatum.cl , claro que no ocupamos framework  viewer, si no que los desarrollamos nosotros mismos, también tenemos sistemas de gestión, control y seguimiento gis, todo esto en Chile lo puedes ver en chaiten.bienes.cl sim2.origenes.cl mtv.mideplan.cl esiir.cnr.cl www.geonodo.cl saludos.

 

Patricio Cifuentes Ithal

Ingeniero en Informática - Ingeniería y Desarrollo de Proyectos

+56(2) 344 06 80 | www.siigsa.cl

--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
www.siigsa.cl

--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
www.siigsa.cl

[Jorge Arévalo]

Hi,

Patricio, thanks for your info, first of all. But, could you please
use english language in this list? I'm spanish, but not all people
understand it. Anyway, many thanks again.

Best regards,
Jorge