[postgis-users] need a help

[Obe, Regina]

Nicholas,
Try instead
 
SELECT r.name
 FROM (SELECT ST_Boundary(the_geom) As b_geom 
	FROM state WHERE  name ~* 'Nana') As s 
INNER JOIN road r ON ST_DWithin(s.b_geom, r.the_geom, 0.001)


1) Buffer is a relatively slow operation in PostGIS.  Faster to do a
distance within check.  
2) As I assume your state is a polygon so if you 
want to find out what runs along the boundary, you want to convert it to
a line string.  Checking against the polygon will give you 
everything inside the state which could be a lot of roads and I don't
think what you want.

3) The above subselect is probably not necessary and in theory shouldn't
make a difference, but writing a subquery  tends to guarantee to force
it to resolve subquery first.  It shouldn't make a difference actually.
I just do it out of habit.

Hope that helps,
Regina


________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Nicholas I
Sent: Tuesday, November 18, 2008 1:36 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] need a help


Hi, 

The objective of the query is to find the roads that run along the state
boundry. I have tried and written a query, can somebody give me
suggestion on it. this below query runs but system hangs. i don't know
what is wrong.

select s.name from state s, road r where
st_intersects(ST_Buffer(s.the_geom, 0.001),r.the_geom) and s.name ~*
'Nana';

-Nicholas I

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.