[postgis-users] Query crashed

[Paul Ramsey]

We'll wait until Mark gets back on the list and comments, probably a  
problem with the function(geometry) && geometry construction and the  
index selectivity calculation.  Mark was working on spatial join  
selectivity a couple months ago, but this might be a case he did not  
anticipate.

P

On 19-Nov-05, at 4:38 PM, Stephen Woodbridge wrote:

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