[postgis-users] Distance constraints on spatial points

[Paragon Corporation]

You don't want to use a min(ST_Distance.. That would be really slow.

Your best bet is using an  EXISTS and use a trigger as fork mentioned.

IF  EXISTS(SELECT geom FROM yourtable WHERE ST_DWITHIN(yourtable.geom,
NEW.geom, your_radius)  ) THEN 
 RAISE EXCEPTION 'Too close';
END IF;

It's too bad the EXCLUSION CONSTRAINTS only works with bounding boxes and I
don't think it supports functions like ST_Expand within the constraint,
though we haven't tried.. 

 That would be a much more succinct way of doing this I think.  Though that
requires PostgreSQL 9.0+ anyway.

http://www.pgcon.org/2010/schedule/events/201.en.html


Leo and Regina,
http://www.postgis.us



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of fork
Sent: Tuesday, May 03, 2011 7:18 PM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Distance constraints on spatial points

Sairam Krishnamurthy <kmsram420 <at> gmail.com> writes:

> 
> Thanks. But how do I specify the code to raise an error if the 
> distance is not met ?

You probably want to write a PLPGSQL function with a SELECT statement that
returns the min(st_distance(NEW.the_geom, a.the_geom)), and raise an
exception if it is below a certain amount.  You might need to multiply by
constants to get the return value into your prefered units. Then you need to
set up a trigger to call it on an insert.

If you don't know how to write PLPGSQL, you need to learn ;)... or maybe
somebody else can help with example code, but even more or less knowing how
to do it, it would take me an hour to write working code, and I cant spare
the time (tho it would be fun).

Or you need to ask a more specific question after posting the code you have
tried -- that would be best.


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users