[postgis-users] Query crashed

[Stephen Woodbridge]

Paul,

Thank you for the help and suggestions. max_distance() sounds like a 
good way around the crash problem, but I get ...

canada=# select count(*)
canada-# from
canada-#   roadseg r, streets s
canada-# where
canada-#   expand(r.the_geom, 0.00007) && s.the_geom and
canada-#   max_distance(s.the_geom, r.the_geom) < 0.00007;
NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column 
references
ERROR:  This function is unimplemented yet

I have the function defined in my database as:

CREATE OR REPLACE FUNCTION max_distance(geometry, geometry)
   RETURNS float8 AS
'/usr/local/lib/liblwgeom.so.1', 'LWGEOM_maxdistance2d_linestring'
   LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION max_distance(geometry, geometry) OWNER TO pgsql;

-Steve

Paul Ramsey wrote:
> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>