[postgis-users] Query crashed

[Paul Ramsey]

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