[postgis-users] Query crashed

[Paul Ramsey]

Oh, one other thing, unless there is something wrong, things which  
are "almost the same" should have bounding boxes that interact with a  
tolerance level of 0.  So you should be able to do the query without  
the expand() at all, and still get the same results.  Not that this  
is the fix for the bug, but it should at least be fast and  
effective :)  (I think the only features for which this would fail  
would be shorter than your tolerance number.)

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

Paul


On 19-Nov-05, at 9:39 PM, Paul Ramsey wrote:

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