[postgis-users] Frank te ha dejado un mensaje...
Frank te ha dejado un mensaje... El mensaje y la persona que lo envió solo te será mostrado a ti y borrarlo en cualquier momento. Puedes responder a través del sistema de intercambio de mensajes. Para descubrir quién te escribió, sigue el siguiente link: http://us1.badoo.com/01143997419/in/APy1rkk8fwE/?lang_id=7 Más gente que también te está esperando: Luigui Marsall (Bogotá, Colombia) Shirley (Cúcuta, Colombia) NiñONIÑO (Cúcuta, Colombia) http://us1.badoo.com/01143997419/in/APy1rkk8fwE/?lang_id=7 Si al pulsar el enlace de este mensaje no funciona, copia y pégalo en la barra de tu navegador. Este email es parte del procedimiento del sistema para el envío de mensajes enviados por Frank. Si has recibido este mensaje por error, ignora este email. Tras un corto periodo de tiempo, será eliminado del sistema. ¡Divértete! El equipo de Badoo Has recibido este email porque un usuario de Badoo te ha dejado un mensaje en Badoo. Este mensaje es automático. Las respuestas a este mensaje no estan controladas y no serán contestadas. Si no quieres recibir más mensajes de Badoo, háznoslo saber: http://us1.badoo.com/impersonation.phtml?lang_id=7&mail_code=63&email=postgis-users%40postgis.refractions.net&secret=&invite_id=572062&user_id=1143997419___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis visual client
Hi,
Le 08/02/2011 19:21, Paragon Corporation a écrit :
John,
We like using OpenJump
for that -- here is a quick tutorial we wrote up on doing Ad
hoc queries with it.
http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html
It works fine with
PostgreSQL 9.0, but if you are using 9.0 -- you need to
download the latest JDBC drivers or set your bytea_output to
escape - both are documented in the FAQ
http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352
Thanks for the good documentation,
Would like to mention an undocumented feature coming with the last
OpenJUMP release.
You can copy the "fence" geometry or the "view extent" in your
query :
button View (Vue in the screenshot) will copy "${view:-1}" where
ever you want in the query (replaced by view extent at execution
time)
button Fence (Cadre in the screenshot) will copy "${fence:-1}" where
ever you want in the query (replaced by the fence geometry at
execution time)
It may help to download small parts of large datasets.
Michaël
Leo
http://www.postgis.us
From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On
Behalf Of John Callahan
Sent: Tuesday, February 08, 2011 12:25 PM
To: PostGIS Users Discussion
Subject: [postgis-users] postgis visual client
I'm sure this has been asked many times but haven't found a
definitive or consensus answer...
Is there a postgis client that supports spatial, ad hoc queries
and returns those results to a map? This would include simple
(and more complex) SELECT statements but also other queries with
spatial results, such as finding nearest neighbors, intersect,
union, etc...
The best idea I found was to create a view from the ad hoc query
and then use a desktop GIS to display that view. You'd need to
update the view (and refresh the GIS screen) to run a new query.
I know QGIS (and other FOSS GIS packages) allows you to create a
definition query (a where clause to subset the layer) and does
support database views. I haven't seen a place where QGIS
supports ad hoc queries. Searching the web, I did find references
to some work done using OpenMap libraries back in 2004, and the
mezoGIS package from around 2005/2006.
Does anyone know of a GUI tool to use, hopefully one that works
for Postgres 9 and PostGIS 2? Thanks.
- John
PS - Thanks for the recommendations on the PostGIS in Action
book. I just purchased it and looking forward to learning what I
can.
**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis visual client
Thanks for the responses. What you describe in your write up with OpenJump is exactly what I'm trying to do. Also, in addition to the QGIS plugins strk mentions, I just found two more possibilities: PgQuery for QGIS and Postgis SQL Editor. Looks like there are some good options available. Thanks again. - John On Tue, Feb 8, 2011 at 1:21 PM, Paragon Corporation wrote: > John, > We like using OpenJump for that -- here is a quick tutorial we wrote up on > doing Ad hoc queries with it. > > > http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html > > It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to > download the latest JDBC drivers or set your bytea_output to escape - both > are documented in the FAQ > > http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352 > > Leo > http://www.postgis.us > > > -- > *From:* postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John > Callahan > *Sent:* Tuesday, February 08, 2011 12:25 PM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] postgis visual client > > I'm sure this has been asked many times but haven't found a definitive or > consensus answer... > > Is there a postgis client that supports spatial, ad hoc queries and returns > those results to a map? This would include simple (and more complex) SELECT > statements but also other queries with spatial results, such as finding > nearest neighbors, intersect, union, etc... > > The best idea I found was to create a view from the ad hoc query and then > use a desktop GIS to display that view. You'd need to update the view (and > refresh the GIS screen) to run a new query. I know QGIS (and other FOSS > GIS packages) allows you to create a definition query (a where clause to > subset the layer) and does support database views. I haven't seen a place > where QGIS supports ad hoc queries. Searching the web, I did find > references to some work done using OpenMap libraries back in 2004, and the > mezoGIS package from around 2005/2006. > > Does anyone know of a GUI tool to use, hopefully one that works for > Postgres 9 and PostGIS 2? Thanks. > > - John > > PS - Thanks for the recommendations on the PostGIS in Action book. I just > purchased it and looking forward to learning what I can. > > ** > John Callahan, Research Scientist > Delaware Geological Survey, University of Delaware > URL: http://www.dgs.udel.edu > ** > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis visual client
If you are looking for a Web-based solution, MapServer should do this as well. David. On Tue, Feb 8, 2011 at 12:21 PM, Paragon Corporation wrote: > John, > We like using OpenJump for that -- here is a quick tutorial we wrote up on > doing Ad hoc queries with it. > > http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html > > It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to > download the latest JDBC drivers or set your bytea_output to escape - both > are documented in the FAQ > > http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352 > > Leo > http://www.postgis.us > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of John > Callahan > Sent: Tuesday, February 08, 2011 12:25 PM > To: PostGIS Users Discussion > Subject: [postgis-users] postgis visual client > > I'm sure this has been asked many times but haven't found a definitive or > consensus answer... > > Is there a postgis client that supports spatial, ad hoc queries and returns > those results to a map? This would include simple (and more complex) SELECT > statements but also other queries with spatial results, such as finding > nearest neighbors, intersect, union, etc... > > The best idea I found was to create a view from the ad hoc query and then > use a desktop GIS to display that view. You'd need to update the view (and > refresh the GIS screen) to run a new query. I know QGIS (and other FOSS > GIS packages) allows you to create a definition query (a where clause to > subset the layer) and does support database views. I haven't seen a place > where QGIS supports ad hoc queries. Searching the web, I did find > references to some work done using OpenMap libraries back in 2004, and the > mezoGIS package from around 2005/2006. > > Does anyone know of a GUI tool to use, hopefully one that works for Postgres > 9 and PostGIS 2? Thanks. > > - John > > PS - Thanks for the recommendations on the PostGIS in Action book. I just > purchased it and looking forward to learning what I can. > > ** > John Callahan, Research Scientist > Delaware Geological Survey, University of Delaware > URL: http://www.dgs.udel.edu > ** > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis visual client
John, We like using OpenJump for that -- here is a quick tutorial we wrote up on doing Ad hoc queries with it. http://www.postgresonline.com/journal/index.php?/archives/72-OpenJump-for-Po stGIS-Spatial-Ad-Hoc-Queries.html It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to download the latest JDBC drivers or set your bytea_output to escape - both are documented in the FAQ http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.html#id2686352 Leo http://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of John Callahan Sent: Tuesday, February 08, 2011 12:25 PM To: PostGIS Users Discussion Subject: [postgis-users] postgis visual client I'm sure this has been asked many times but haven't found a definitive or consensus answer... Is there a postgis client that supports spatial, ad hoc queries and returns those results to a map? This would include simple (and more complex) SELECT statements but also other queries with spatial results, such as finding nearest neighbors, intersect, union, etc... The best idea I found was to create a view from the ad hoc query and then use a desktop GIS to display that view. You'd need to update the view (and refresh the GIS screen) to run a new query. I know QGIS (and other FOSS GIS packages) allows you to create a definition query (a where clause to subset the layer) and does support database views. I haven't seen a place where QGIS supports ad hoc queries. Searching the web, I did find references to some work done using OpenMap libraries back in 2004, and the mezoGIS package from around 2005/2006. Does anyone know of a GUI tool to use, hopefully one that works for Postgres 9 and PostGIS 2? Thanks. - John PS - Thanks for the recommendations on the PostGIS in Action book. I just purchased it and looking forward to learning what I can. ** John Callahan, Research Scientist Delaware Geological Survey, University of Delaware URL: http://www.dgs.udel.edu ** ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [GENERAL] How to improve this query?
Hi >> I thought hash indexes were discouraged over btree. Anyway, I'll test it. > > Absolutely. You do not want to be using Hash Indexes these days - stick with > B-Tree. The hash refers to the method by which the executor matches the two > column fields and is not related to the underlying index type. Yeah, I know that btrees are the preferred way - except for the match case "=". That's at least what docu. implicitly says: http://www.postgresql.org/docs/9.0/static/indexes-types.html So the note there is incomplete? > Caution: Hash index operations are not presently WAL-logged, so hash > indexes might need to be rebuilt with REINDEX after a database crash. > They are also not replicated over streaming or file-based replication. > For these reasons, hash index use is presently discouraged. ... and should also note, that hashes even don't outperform btrees for = operator? Yours, S. 2011/2/8 Mark Cave-Ayland : > On 08/02/11 11:05, Jorge Arévalo wrote: > >> 2011/2/8 Stefan Keller: >>> >>> " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" >>> tells me that these two longish strings are compared first. >>> Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD >>> as hash instead btree? >> >> I thought hash indexes were discouraged over btree. Anyway, I'll test it. > > Absolutely. You do not want to be using Hash Indexes these days - stick with > B-Tree. The hash refers to the method by which the executor matches the two > column fields and is not related to the underlying index type. > > > ATB, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Core dumps
On Tue, Feb 08, 2011 at 06:31:23PM +0100, strk wrote: > I see pg_parser_errhint is calling lwmessage_truncate and passing > it (lwg_parser_result->errlocation-1) w/out first checking > errlocation begin <= 0 (checked shortly after). I've committed a fix for the problem of my guess in r6789. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis visual client
On Tue, Feb 08, 2011 at 12:25:12PM -0500, John Callahan wrote: > I haven't seen a place where QGIS supports ad hoc queries. See RT Sql Layer [1][2] from faunalia plugins repository [3] [1] http://www.faunalia.it/qgis/rt_sql_layer.zip [2] http://www.faunalia.it/qgis/rt_sql_layer.experimental.zip [3] http://www.faunalia.it/qgis/plugins.xml --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Core dumps
On Tue, Feb 08, 2011 at 08:54:12AM +0100, Peter Hopfgartner wrote: > >> Program terminated with signal 11, Segmentation fault. > >> #0 0x0035eee79c00 in strncat () from /lib64/libc.so.6 > >> (gdb) bt > >> #0 0x0035eee79c00 in strncat () from /lib64/libc.so.6 > >> #1 0x2b9176d04282 in lwmessage_truncate () > >> from /usr/lib64/pgsql/postgis-1.5.so > >> #2 0x2b9176cc95a1 in pg_parser_errhint () > >> from /usr/lib64/pgsql/postgis-1.5.so > >> #3 0x2b9176ccf2eb in LWGEOM_in () from > >/usr/lib64/pgsql/postgis-1.5.so I see pg_parser_errhint is calling lwmessage_truncate and passing it (lwg_parser_result->errlocation-1) w/out first checking errlocation begin <= 0 (checked shortly after). postgis/lwgeom_pg.c:68 // use postgis/lwgeom_pg.c:72 // checks for > 0 Could be a possible cause. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] postgis visual client
I'm sure this has been asked many times but haven't found a definitive or consensus answer... Is there a postgis client that supports spatial, ad hoc queries and returns those results to a map? This would include simple (and more complex) SELECT statements but also other queries with spatial results, such as finding nearest neighbors, intersect, union, etc... The best idea I found was to create a view from the ad hoc query and then use a desktop GIS to display that view. You'd need to update the view (and refresh the GIS screen) to run a new query. I know QGIS (and other FOSS GIS packages) allows you to create a definition query (a where clause to subset the layer) and does support database views. I haven't seen a place where QGIS supports ad hoc queries. Searching the web, I did find references to some work done using OpenMap libraries back in 2004, and the mezoGIS package from around 2005/2006. Does anyone know of a GUI tool to use, hopefully one that works for Postgres 9 and PostGIS 2? Thanks. - John PS - Thanks for the recommendations on the PostGIS in Action book. I just purchased it and looking forward to learning what I can. ** John Callahan, Research Scientist Delaware Geological Survey, University of Delaware URL: http://www.dgs.udel.edu ** ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] efficiently testing for points near polygons
Hallo The overall performance will be better if you use ST_Dwithin instead of buffer and intersects. But if the buffer is done once and will never happen again maybe there is not much gain from that. The bounding boxes is very important here. The index works by first finding overlapping bounding boxes and then from that do a recheck and calculate if the real geometries actually is intersecting (or within the given distance) Because of that it is better the smaller your geometries are which means you should absolutely not union them together. If you have whole continents in one polygon the index will be totally worthless because most points will fall inside the bounding box anyway. So it might be difficult to get good performance out of a dataset with very big geoemtries, but there are techniques to slice them up and index the smaller parts instead. To get a good description about that "PostGIS in Action" is a good source of knowledge (http://manning.com/obe) /Nicklas On Tue, 2011-02-08 at 14:47 +0100, David Kaplan wrote: > Hi, > > I have a large set of oceanic point data that I need to test to identify > all points that are within a certain distance of land. I initially > tried doing the most obvious thing I could think of - buffer land > polygons and test points for intersection, but this is taking too long > for the full dataset and is giving me strange "terminating connection > due to administrator command" errors. I imagine that there is something > in my initial strategy that doesn't scale well, but it is hard for me to > identify what is the best strategy. Hopefully someone already knows the > answer and can give me a hand. > > Here are some concrete questions that I hope someone can help me with: > > 1) How exactly are indexes used for point data? AS BBox doesn't make > much sense (at least to me), I imagine that this somehow groups > identical points so that operations only have to be done once for each > distinct point. Is this the case? > > 2) I initially made a function to test for proximity to land: > > CREATE OR REPLACE FUNCTION near_land(geo geometry) > RETURNS boolean AS > $BODY$ > SELECT bool_or( ST_Intersects($1,wkb_geometry_5km_buf) ) > FROM gshhs_h_l1; > $BODY$ > LANGUAGE 'sql' STABLE; > > This works fast for small amounts of data, but I have a feeling that > this prohibits using indexes for the full dataset and therefore could be > causing much of my slowness. Is this so? > > 3) Would it be best to ST_Union all my polygons before making the > comparison, knowing that the polygons don't overlap? Or is this no > different than just doing the intersect and then using bool_or? > > 4) Would using long transaction support help me avoid the "administrator > command" errors? I don't really know what long transaction support > does, but my transaction certainly is long... > > Thanks for the help. > > Cheers, > David > > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipolygom
Hallo Marcel
i have not been playing that much myself with geography type but since
you already have your data in srid 4326 I guess you could just do
something like:
ALTER TABLE zip_polygon add column geog geography;
UPDATE zip_polygon set geog=geom::geography;
create index idx_zpoly_geog
on zip_polygon
using gist(geog);
vacuum analyze zip_polygon;
Then you should have the geography version of your geometries in column
geog and it should be indexed and analyzed.
/Nicklas
On Tue, 2011-02-08 at 13:33 +0100, Marcel Kroschel wrote:
> Hi Nicklas,
> your example works - thank you.
> How can i store it in geograpgy type?
> thx
> Marcel
>
> - Ursprüngliche Mail -
> Von: "Nicklas Avén"
> An: "PostGIS Users Discussion"
> Gesendet: Dienstag, 8. Februar 2011 12:46:14
> Betreff: Re: [postgis-users] multipolygom
>
> To get the distance in meters you can use the geography type instead.
>
> something like:
>
> SELECT *
> FROM zip_polygon
> WHERE ST_DWithin(geom::geography, ST_GeogFromText('4326;POINT(13.171982
> 52.52646)'), 2);
>
> But to get it working fast you should store in geograpgy type and build
> your spatial index n that.
>
> HTH
>
> Nicklas
>
>
>
> On Tue, 2011-02-08 at 12:29 +0100, Marcel Kroschel wrote:
> > hi @all.
> > Hope someone can help me ;)
> > I uses postgis 1.5.2., create a table with with multipolygon and lng/lat
> > coordinates called zip_polygon
> >
> > SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326, 'MULTIPOLYGON', 2 );
> >
> > When ich want all polygons in distance i use this query:
> > SELECT *
> > FROM zip_polygon
> > WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)', 4326),
> > 0.02);
> >
> > But the manual say, that the last entry is the distance in meter. When i
> > replace the 0.02 to the value 1, then i get the whole list.
> >
> > example of geom - entry (SELECT (ST_AsText(geom)) from zip_polygon zp):
> > "MULTIPOLYGON(((13.69375 50.82966,13.69252 50.83034,13.68925
> > 50.83206,13.68766 50.83374,13.68639 50.83576,13.68399 50.83782,13.68235
> > 50.8386,13.72514 50.81991)))"
> >
> > greeting
> > Marcel
> >
> >
> > ___
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] efficiently testing for points near polygons
Hi, I have a large set of oceanic point data that I need to test to identify all points that are within a certain distance of land. I initially tried doing the most obvious thing I could think of - buffer land polygons and test points for intersection, but this is taking too long for the full dataset and is giving me strange "terminating connection due to administrator command" errors. I imagine that there is something in my initial strategy that doesn't scale well, but it is hard for me to identify what is the best strategy. Hopefully someone already knows the answer and can give me a hand. Here are some concrete questions that I hope someone can help me with: 1) How exactly are indexes used for point data? AS BBox doesn't make much sense (at least to me), I imagine that this somehow groups identical points so that operations only have to be done once for each distinct point. Is this the case? 2) I initially made a function to test for proximity to land: CREATE OR REPLACE FUNCTION near_land(geo geometry) RETURNS boolean AS $BODY$ SELECT bool_or( ST_Intersects($1,wkb_geometry_5km_buf) ) FROM gshhs_h_l1; $BODY$ LANGUAGE 'sql' STABLE; This works fast for small amounts of data, but I have a feeling that this prohibits using indexes for the full dataset and therefore could be causing much of my slowness. Is this so? 3) Would it be best to ST_Union all my polygons before making the comparison, knowing that the polygons don't overlap? Or is this no different than just doing the intersect and then using bool_or? 4) Would using long transaction support help me avoid the "administrator command" errors? I don't really know what long transaction support does, but my transaction certainly is long... Thanks for the help. Cheers, David -- ** David M. Kaplan Charge de Recherche 1 Institut de Recherche pour le Developpement Centre de Recherche Halieutique Mediterraneenne et Tropicale av. Jean Monnet B.P. 171 34203 Sete cedex France Phone: +33 (0)4 99 57 32 27 Fax: +33 (0)4 99 57 32 95 http://www.ur097.ird.fr/team/dkaplan/index.html http://www.amped.ird.fr/ ** ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipolygom
Hi Nicklas,
your example works - thank you.
How can i store it in geograpgy type?
thx
Marcel
- Ursprüngliche Mail -
Von: "Nicklas Avén"
An: "PostGIS Users Discussion"
Gesendet: Dienstag, 8. Februar 2011 12:46:14
Betreff: Re: [postgis-users] multipolygom
To get the distance in meters you can use the geography type instead.
something like:
SELECT *
FROM zip_polygon
WHERE ST_DWithin(geom::geography, ST_GeogFromText('4326;POINT(13.171982
52.52646)'), 2);
But to get it working fast you should store in geograpgy type and build
your spatial index n that.
HTH
Nicklas
On Tue, 2011-02-08 at 12:29 +0100, Marcel Kroschel wrote:
> hi @all.
> Hope someone can help me ;)
> I uses postgis 1.5.2., create a table with with multipolygon and lng/lat
> coordinates called zip_polygon
>
> SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326, 'MULTIPOLYGON', 2 );
>
> When ich want all polygons in distance i use this query:
> SELECT *
> FROM zip_polygon
> WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)', 4326),
> 0.02);
>
> But the manual say, that the last entry is the distance in meter. When i
> replace the 0.02 to the value 1, then i get the whole list.
>
> example of geom - entry (SELECT (ST_AsText(geom)) from zip_polygon zp):
> "MULTIPOLYGON(((13.69375 50.82966,13.69252 50.83034,13.68925
> 50.83206,13.68766 50.83374,13.68639 50.83576,13.68399 50.83782,13.68235
> 50.8386,13.72514 50.81991)))"
>
> greeting
> Marcel
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipolygom
Hello Stephan,
and which number is for lat/lng?
- Ursprüngliche Mail -
Von: "Stephan Holl"
An: postgis-users@postgis.refractions.net
Gesendet: Dienstag, 8. Februar 2011 12:34:41
Betreff: Re: [postgis-users] multipolygom
Hello Marcel,
Marcel Kroschel , [20110208 - 12:29:18]
> hi @all.
> Hope someone can help me ;)
> I uses postgis 1.5.2., create a table with with multipolygon and
> lng/lat coordinates called zip_polygon
>
> SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326,
> 'MULTIPOLYGON', 2 );
>
> When ich want all polygons in distance i use this query:
> SELECT *
> FROM zip_polygon
> WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)',
> 4326), 0.02);
>
> But the manual say, that the last entry is the distance in meter.
> When i replace the 0.02 to the value 1, then i get the whole list.
AFAIK it uses the map-units, which is in your case decimal degrees
(epsg:4326).
HTH
Stephan
--
Stephan Holl | Tel.: +49 (0)541-33 508 3663
Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998
Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [GENERAL] How to improve this query?
On Tue, Feb 8, 2011 at 12:42 PM, Mark Cave-Ayland wrote: > On 08/02/11 11:05, Jorge Arévalo wrote: > >> 2011/2/8 Stefan Keller: >>> >>> " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" >>> tells me that these two longish strings are compared first. >>> Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD >>> as hash instead btree? >> >> I thought hash indexes were discouraged over btree. Anyway, I'll test it. > > Absolutely. You do not want to be using Hash Indexes these days - stick with > B-Tree. The hash refers to the method by which the executor matches the two > column fields and is not related to the underlying index type. > Sorry, buy I'm a bit lost here. Then, should I create indexes using btree as I did? How could I intervene in the way executor matches columns? Apart from this, I've modified some config parameters in postgresql.conf: - autovacuum set to OFF - shared_buffers set to 256MB - random_page_cost set to 2.0 - effective_cache_size set to 1024MB For changing shared buffers I needed to set the kernel.shmmax parameter to 1024MB (was 32MB) in my OS (Ubuntu 10.04) with sysctl. My machine has 4GB RAM, and I pretend to use it as a postgresql dedicated server. With these changes, now the query plan looks like this: "Hash Join (cost=3.98..211606.88 rows=209816 width=497)" " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" " Join Filter: ((TABLE_A.INT_FIELD2 >= TABLE_B.min) AND (TABLE_A.INT_FIELD2 <= TABLE_B.max))" " -> Nested Loop (cost=0.00..179737.10 rows=472086 width=543)" "Join Filter: _st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom)" "-> Seq Scan using TABLE_C_string_idx on TABLE_C (cost=0.00..3067.52 rows=69352 width=1275)" "-> Index Scan using TABLE_A_geom_idx on TABLE_A (cost=0.00..1.50 rows=4 width=493)" " Index Cond: (TABLE_A.wkb_geometry && TABLE_C.the_geom)" " -> Hash (cost=2.88..2.88 rows=88 width=63)" "-> Seq Scan on TABLE_B (cost=0.00..2.88 rows=88 width=63)" So, the total cost in join and scans is lower (I guess due to random_page_cost changing), and now a seq scan is planned in the string comparison against the index scan planned before. I don't know if I'm really improving something or I'm flying blind... The query is running again. Thanks! -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://es.linkedin.com/in/jorgearevalo80 http://mobility.grupodeimos.com/ http://www.twitter.com/jorgeas80 http://gis4free.wordpress.com http://geohash.org/ezjqgrgzz0g > > ATB, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipolygom
To get the distance in meters you can use the geography type instead.
something like:
SELECT *
FROM zip_polygon
WHERE ST_DWithin(geom::geography, ST_GeogFromText('4326;POINT(13.171982
52.52646)'), 2);
But to get it working fast you should store in geograpgy type and build
your spatial index n that.
HTH
Nicklas
On Tue, 2011-02-08 at 12:29 +0100, Marcel Kroschel wrote:
> hi @all.
> Hope someone can help me ;)
> I uses postgis 1.5.2., create a table with with multipolygon and lng/lat
> coordinates called zip_polygon
>
> SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326, 'MULTIPOLYGON', 2 );
>
> When ich want all polygons in distance i use this query:
> SELECT *
> FROM zip_polygon
> WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)', 4326),
> 0.02);
>
> But the manual say, that the last entry is the distance in meter. When i
> replace the 0.02 to the value 1, then i get the whole list.
>
> example of geom - entry (SELECT (ST_AsText(geom)) from zip_polygon zp):
> "MULTIPOLYGON(((13.69375 50.82966,13.69252 50.83034,13.68925
> 50.83206,13.68766 50.83374,13.68639 50.83576,13.68399 50.83782,13.68235
> 50.8386,13.72514 50.81991)))"
>
> greeting
> Marcel
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [GENERAL] How to improve this query?
On 08/02/11 11:05, Jorge Arévalo wrote: 2011/2/8 Stefan Keller: " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" tells me that these two longish strings are compared first. Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD as hash instead btree? I thought hash indexes were discouraged over btree. Anyway, I'll test it. Absolutely. You do not want to be using Hash Indexes these days - stick with B-Tree. The hash refers to the method by which the executor matches the two column fields and is not related to the underlying index type. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipolygom
Hello Marcel,
Marcel Kroschel , [20110208 - 12:29:18]
> hi @all.
> Hope someone can help me ;)
> I uses postgis 1.5.2., create a table with with multipolygon and
> lng/lat coordinates called zip_polygon
>
> SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326,
> 'MULTIPOLYGON', 2 );
>
> When ich want all polygons in distance i use this query:
> SELECT *
> FROM zip_polygon
> WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)',
> 4326), 0.02);
>
> But the manual say, that the last entry is the distance in meter.
> When i replace the 0.02 to the value 1, then i get the whole list.
AFAIK it uses the map-units, which is in your case decimal degrees
(epsg:4326).
HTH
Stephan
--
Stephan Holl | Tel.: +49 (0)541-33 508 3663
Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998
Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
signature.asc
Description: PGP signature
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] multipolygom
hi @all.
Hope someone can help me ;)
I uses postgis 1.5.2., create a table with with multipolygon and lng/lat
coordinates called zip_polygon
SELECT AddGeometryColumn ( 'zip_polygon', 'geom', 4326, 'MULTIPOLYGON', 2 );
When ich want all polygons in distance i use this query:
SELECT *
FROM zip_polygon
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(13.171982 52.52646)', 4326),
0.02);
But the manual say, that the last entry is the distance in meter. When i
replace the 0.02 to the value 1, then i get the whole list.
example of geom - entry (SELECT (ST_AsText(geom)) from zip_polygon zp):
"MULTIPOLYGON(((13.69375 50.82966,13.69252 50.83034,13.68925 50.83206,13.68766
50.83374,13.68639 50.83576,13.68399 50.83782,13.68235 50.8386,13.72514
50.81991)))"
greeting
Marcel
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [GENERAL] How to improve this query?
Hi, 2011/2/8 Stefan Keller : > " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" > tells me that these two longish strings are compared first. > Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD > as hash instead btree? I thought hash indexes were discouraged over btree. Anyway, I'll test it. > Did you set autovacuum off during update? Forgot it. I'll do it > transaction level? I don't understand this very well... > > Yours, S. > Many thanks! -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://es.linkedin.com/in/jorgearevalo80 http://mobility.grupodeimos.com/ http://www.twitter.com/jorgeas80 http://gis4free.wordpress.com http://geohash.org/ezjqgrgzz0g > 2011/2/7 Jorge Arévalo : >> Hello, >> >> update TABLE_A set INT_FIELD = TABLE_B.INT_FIELD from TABLE_C, TABLE_B >> where TABLE_B.STR_FIELD = TABLE_C.STR_FIELD and TABLE_A.INT_FIELD2 >= >> TABLE_B.min and TABLE_A.INT_FIELD2 <= TABLE_B.max and >> st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom); >> >> Tables description: >> >> TABLE A >> >> CREATE TABLE TABLE_A >> ( >> ogc_fid serial NOT NULL, >> wkb_geometry geometry, >> INT_FIELD2 integer, >> INT_FIELD integer NOT NULL DEFAULT 0, >> CONSTRAINT TABLE_A_pk PRIMARY KEY (ogc_fid), >> CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2), >> CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 23030) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_A_geom_idx >> ON TABLE_A >> USING gist >> (wkb_geometry); >> >> CREATE INDEX TABLE_A_INT_FIELD2 >> ON TABLE_A >> USING btree >> (INT_FIELD2); >> >> +++ >> >> >> TABLE B >> >> CREATE TABLE TABLE_B >> ( >> STR_FIELD character(50) DEFAULT NULL::bpchar, >> min integer NOT NULL DEFAULT 0, >> max integer NOT NULL DEFAULT 0, >> INT_FIELD integer NOT NULL DEFAULT 0, >> oid integer NOT NULL DEFAULT 0, >> CONSTRAINT TABLE_B_pk PRIMARY KEY (oid) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_B_idx >> ON TABLE_B >> USING btree >> (STR_FIELD); >> >> CREATE INDEX TABLE_B_max_idx >> ON TABLE_B >> USING btree >> (max); >> >> CREATE INDEX TABLE_B_min_idx >> ON TABLE_B >> USING btree >> (min); >> >> +++ >> >> >> >> TABLE C >> >> CREATE TABLE TABLE_C >> ( >> the_geom geometry, >> STR_FIELD character(50) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_C_index >> ON TABLE_C >> USING gist >> (the_geom); >> >> CREATE INDEX TABLE_C_string_idx >> ON TABLE_C >> USING btree >> (STR_FIELD); >> >> +++ >> >> >> Tables data: >> >> - TABLE_A: 896888 entries. The geometries are single polygons >> (squares, actually), coordinates are floating point numbers >> - TABLE_B: 88 entries. >> - TABLE C: 69352 entries. Geometries are single polygons too, but much >> bigger than the polygons from TABLE_A. >> >> As you can see in the query, I'm interested in the polygons of TABLE_A >> that intersects the big polygons in TABLE_C. >> >> Query plan (explain output): >> >> "Hash Join (cost=3.98..986808.75 rows=209049 width=497)" >> " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" >> " Join Filter: ((TABLE_A.INT_FIELD2 >= TABLE_B.min) AND >> (TABLE_A.INT_FIELD2 <= TABLE_B.max))" >> " -> Nested Loop (cost=0.00..955055.47 rows=470360 width=543)" >> " Join Filter: _st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom)" >> " -> Index Scan using TABLE_C_string_idx on TABLE_C >> (cost=0.00..8044.56 rows=69352 width=1517)" >> " -> Index Scan using TABLE_A_geom_idx on TABLE_A >> (cost=0.00..12.61 rows=4 width=493)" >> " Index Cond: (TABLE_A.wkb_geometry && TABLE_C.the_geom)" >> " -> Hash (cost=2.88..2.88 rows=88 width=63)" >> " -> Seq Scan on TABLE_B (cost=0.00..2.88 rows=88 width=63)" >> >> >> With that information, how could I make the update faster? It takes >> more than 24hours to finish. >> >> >> Many thanks in advance >> >> >> Best regards, >> >> -- >> Jorge Arévalo >> Internet & Mobilty Division, DEIMOS >> jorge.arev...@deimos-space.com >> http://es.linkedin.com/in/jorgearevalo80 >> http://mobility.grupodeimos.com/ >> http://www.twitter.com/jorgeas80 >> http://gis4free.wordpress.com >> http://geohash.org/ezjqgrgzz0g >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [GENERAL] How to improve this query?
More possibilities (just ideas) before update: * VACUUM FULL and reindex after that. If not, your indexes became bloated. * If you've got the RAM, just use more of it, 1 or 2 GB. You can do this at runtime: SET maintenance_work_mem="180MB". If you can fit whole tables into memory, reindexing should be fast. * You may want to increase checkpoint_segments in postgresql.conf. You can reload config settings without restarting database: http://heatware.net/databases/postgresql-reload-config-without-restarting/ Yours, S. 2011/2/8 Stefan Keller : > " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" > tells me that these two longish strings are compared first. > Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD > as hash instead btree? > Did you set autovacuum off during update? transaction level? > > Yours, S. > > 2011/2/7 Jorge Arévalo : >> Hello, >> >> update TABLE_A set INT_FIELD = TABLE_B.INT_FIELD from TABLE_C, TABLE_B >> where TABLE_B.STR_FIELD = TABLE_C.STR_FIELD and TABLE_A.INT_FIELD2 >= >> TABLE_B.min and TABLE_A.INT_FIELD2 <= TABLE_B.max and >> st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom); >> >> Tables description: >> >> TABLE A >> >> CREATE TABLE TABLE_A >> ( >> ogc_fid serial NOT NULL, >> wkb_geometry geometry, >> INT_FIELD2 integer, >> INT_FIELD integer NOT NULL DEFAULT 0, >> CONSTRAINT TABLE_A_pk PRIMARY KEY (ogc_fid), >> CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2), >> CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 23030) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_A_geom_idx >> ON TABLE_A >> USING gist >> (wkb_geometry); >> >> CREATE INDEX TABLE_A_INT_FIELD2 >> ON TABLE_A >> USING btree >> (INT_FIELD2); >> >> +++ >> >> >> TABLE B >> >> CREATE TABLE TABLE_B >> ( >> STR_FIELD character(50) DEFAULT NULL::bpchar, >> min integer NOT NULL DEFAULT 0, >> max integer NOT NULL DEFAULT 0, >> INT_FIELD integer NOT NULL DEFAULT 0, >> oid integer NOT NULL DEFAULT 0, >> CONSTRAINT TABLE_B_pk PRIMARY KEY (oid) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_B_idx >> ON TABLE_B >> USING btree >> (STR_FIELD); >> >> CREATE INDEX TABLE_B_max_idx >> ON TABLE_B >> USING btree >> (max); >> >> CREATE INDEX TABLE_B_min_idx >> ON TABLE_B >> USING btree >> (min); >> >> +++ >> >> >> >> TABLE C >> >> CREATE TABLE TABLE_C >> ( >> the_geom geometry, >> STR_FIELD character(50) >> ) >> WITH ( >> OIDS=FALSE >> ); >> >> CREATE INDEX TABLE_C_index >> ON TABLE_C >> USING gist >> (the_geom); >> >> CREATE INDEX TABLE_C_string_idx >> ON TABLE_C >> USING btree >> (STR_FIELD); >> >> +++ >> >> >> Tables data: >> >> - TABLE_A: 896888 entries. The geometries are single polygons >> (squares, actually), coordinates are floating point numbers >> - TABLE_B: 88 entries. >> - TABLE C: 69352 entries. Geometries are single polygons too, but much >> bigger than the polygons from TABLE_A. >> >> As you can see in the query, I'm interested in the polygons of TABLE_A >> that intersects the big polygons in TABLE_C. >> >> Query plan (explain output): >> >> "Hash Join (cost=3.98..986808.75 rows=209049 width=497)" >> " Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)" >> " Join Filter: ((TABLE_A.INT_FIELD2 >= TABLE_B.min) AND >> (TABLE_A.INT_FIELD2 <= TABLE_B.max))" >> " -> Nested Loop (cost=0.00..955055.47 rows=470360 width=543)" >> " Join Filter: _st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom)" >> " -> Index Scan using TABLE_C_string_idx on TABLE_C >> (cost=0.00..8044.56 rows=69352 width=1517)" >> " -> Index Scan using TABLE_A_geom_idx on TABLE_A >> (cost=0.00..12.61 rows=4 width=493)" >> " Index Cond: (TABLE_A.wkb_geometry && TABLE_C.the_geom)" >> " -> Hash (cost=2.88..2.88 rows=88 width=63)" >> " -> Seq Scan on TABLE_B (cost=0.00..2.88 rows=88 width=63)" >> >> >> With that information, how could I make the update faster? It takes >> more than 24hours to finish. >> >> >> Many thanks in advance >> >> >> Best regards, >> >> -- >> Jorge Arévalo >> Internet & Mobilty Division, DEIMOS >> jorge.arev...@deimos-space.com >> http://es.linkedin.com/in/jorgearevalo80 >> http://mobility.grupodeimos.com/ >> http://www.twitter.com/jorgeas80 >> http://gis4free.wordpress.com >> http://geohash.org/ezjqgrgzz0g >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
