[postgis-users] Query crashed

[Paul Ramsey]

Just realized, there is a max_distance() function in PostGIS, so you  
can make replace the within(buffer()) call with something much faster  
that is almost semantically equivalent:

select count(*)
from
   roadseg r, streets s
where
   expand(r.the_geom, 0.00007) && s.the_geom and
   max_distance(s.the_geom, r.the_geom) < 0.00007;

The semantics of max_distance are here:

<http://postgis.refractions.net/pipermail/postgis-users/2002-January/ 
000554.html>

P.

On 19-Nov-05, at 2:29 PM, Paul Ramsey wrote:

> Well, you crashed the backend... probably with the buffer(), though  
> the gist_joinsel error is oddball.  Oh, your GEOS is quite over a  
> year old and a lot of bugs have been scrubbed since then, so an  
> upgrade there would help in general.
>
> Finally, your query syntax is hell, here is why:
>
> buffer(foo) && somethingelse
>   you are doing an incredibly expensive geometry operation (buffer 
> ()) and then only comparing the bounding box!  use expand() instead
>
> within(something,buffer(foo))
>   well, I see what you are doing here, and there is currently no  
> other way. you are looking for items which are "mostly equal" to  
> other items.  that is, they may have had some coordinate drift do  
> to rounding errors, or been noded in places, or had some vertices  
> added or removed, but they basically describe the same line on the  
> ground.  what is really needed is a kindaequals 
> (geom_a,geom_b,tolerance) function that returns a number of values,  
> depending on the equality case: 1 = completely equal, 2 = describe  
> the same line within tolerance, 3 = geom_a is same as geom_b but  
> shorter, 4 = geom_b is same as geom_a but shorter.
>
> P.
>
> On 19-Nov-05, at 1:56 PM, Stephen Woodbridge wrote:
>
>> Hi all,
>>
>> I was running the following query:
>>
>> select count(*)
>> from
>>   roadseg r, streets s
>> where
>>   buffer(r.the_geom, 0.00007) && s.the_geom and
>>   within(s.the_geom, buffer(r.the_geom, 0.00007));
>>
>> And it crashed with the following error message after running for  
>> 1,959,844 ms in pgadmin3:
>>
>> NOTICE:  LWGEOM_gist_joinsel called with arguments that are not  
>> column references
>> server closed the connection unexpectedly
>> 	This probably means the server terminated abnormally
>> 	before or while processing the request.
>>
>> The "roadseg" table is the geobase roadseg data for all provinces  
>> loaded into a table and the "streets" is the same for the Census  
>> streets data for all provinces.
>>
>> postgis_full_version() reports:
>> POSTGIS="1.0.4"
>> GEOS="2.0.1"
>> PROJ="Rel. 4.4.9, 29 Oct 2004"
>> USE_STATS DBPROC="0.3.0"
>> RELPROC="0.3.0"
>>
>> and version() reports:
>>
>> "PostgreSQL 8.0.4 on i386-portbld-freebsd5.3, compiled by GCC cc  
>> (GCC) 3.4.2 [FreeBSD] 20040728"
>>
>> Any ideas on what this is about. All the data was loaded via  
>> shp2pgsql.
>>
>> Thanks,
>>   -Steve
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users