[postgis-users] st_within too slow?

guido.lemoine at jrc.it guido.lemoine at jrc.it
Sat Aug 30 04:57:11 PDT 2008


Try this instead:

SELECT road_network, toponymy FROM maps AS m WHERE m.b_box &&
geomfromtext('POINT($lon $lat)',4326) and
st_within(geomfromtext('POINT($lon $lat)',4326), m.b_box)

of course, if you already know that the point is in Portugal,
you could do:

SELECT road_network, toponymy FROM maps AS m WHERE 
m.country = 'PT' and 
m.b_box && geomfromtext('POINT($lon $lat)',4326)

(assuming that the maps table has the 'country' column with
country names).


>-- Original Message --
>From: Pedro Doria Meunier <pdoria at netmadeira.com>
>To: postgis-users at postgis.refractions.net
>Date: Sat, 30 Aug 2008 12:06:38 +0100
>Subject: [postgis-users] st_within too slow?
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Hi All,
>I was using the legacy operator && for determining if a point was inside
>a particular geometry as thus:
>SELECT road_network, toponymy FROM maps AS m WHERE m.b_box &&
>geomfromtext('POINT($lon $lat)',4326)
>When I was faced with two geometries such as Portugal and Spain country
>boundaries I began always getting the row for Spain when I was trying to
>get the map of Portugal for a point inside it.
>It led me to the conclusion that the && operates as a bounding box for
>the corresponding geometry where, in this particular case, the bounding
>box of Spain *overlaps* the one of Portugal.
>So I was forced to update the query to this:
>SELECT road_network, toponymy FROM maps AS m WHERE
>st_within(geomfromtext('POINT($lon $lat)',4326), m.b_box)
>It works but with a catch: it takes ~200ms where the && took
>~25ms ... :-(
>Before you ask: yes, I've created the correspondent indices using gist.
>Is there any way I can work around this or optimize it?
>P.S. I'm using PostgreSQL 8.2.9 on x86_64, PostGIS 1.3.3, geos 2.2.3,
>proj 4.5.0
>Thank you in advance for any insight on the matter.
>Pedro Doria Meunier <pdoria at netmadeira.com>
>Attachment: signature.asc
>postgis-users mailing list
>postgis-users at postgis.refractions.net

More information about the postgis-users mailing list