[postgis-users] Triggers and Spatial

[Romi Hardiyanto]

For about 8 months, i have been using this trigger:

CREATE OR REPLACE FUNCTION "public"."tr_position_geopoint" () RETURNS 
trigger AS'
BEGIN
   IF (NEW.longitude IS NOT NULL OR NEW.longitude = 0) AND (NEW.latitude 
IS NOT NULL OR NEW.latitude = 0) THEN
     BEGIN
       NEW.geopoint=''POINT('' || NEW.longitude || '' '' || NEW.latitude 
|| '')'';
       -- rough a hundred meter (3 sec)
       SELECT INTO NEW.location_road_name name FROM t_location_road 
WHERE the_geom && expand(NEW.geopoint,0.000278) ORDER BY 
distance(the_geom,NEW.geopoint) ASC LIMIT 1 OFFSET 0;
       IF NEW.location_road_name IS null THEN
         -- rough 30 meters (1 sec)
         SELECT INTO NEW.location_road_name name FROM t_location_road 
WHERE the_geom && expand(NEW.geopoint,0.000833) ORDER BY 
distance(the_geom,NEW.geopoint) ASC LIMIT 1 OFFSET 0;
       END IF;
       RETURN NEW;
     END;
   END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

the geopoint is geometry type (POINT), longitude and latitude is double
the t_location_road.the_geom is database of linestrings

Romi H

Paul Ramsey wrote:
> Indeed it does apply, and should be an often used arrow in the spatial 
> database designers quiver. For example, that table full of street 
> addresses that you want to be spatial? Add a POINT column to it, and add 
> a trigger on insert and update that runs a geocoding process and 
> populates the column. Then procede as normal.
> 
> Or, say you have a parcel fabric and you want titles to have notations 
> about rights of way that overlap. Throw away that mylar! Capture your 
> rights-of-way spatially, and add a trigger to your RoW table that does 
> an overlay with parcels on insert and update, and adds the notations 
> automatically. Heck, then add a trigger to the parcel table that 
> populates a mailing list table of people who need to be notified about 
> new encumberances.
> 
> It goes on and on... ;) Managing spatial data should not be a pain, it 
> should be a largely automated addition to your normal workflow. The only 
> addition should be that you capture the spatial component of your data 
> as soon as possible (preferably the same time you capture any 
> information at all) and get it into the system. Once there, it can flow 
> unimpeded through the rest of your processes and add value at almost 
> every turn.
> 
> P.
> 
> chodgson at refractions.net wrote:
> 
>> I've actually never written a trigger, so the manual will be able to 
>> tell you how to do it better than me... I just know the concept and 
>> thought it would apply.
>>
>> Chris
>>
>> Quoting Tyler Mitchell <TMitchell at lignum.com>:
>>
>>
>>>> It would be possible to put a trigger on your table that took the
>>>> float columns
>>>> and created a point or line geometry out of them, and updated a
>>>> geometry column
>>>> in your database, every time a row was inserted or updated. Then
>>>
>>>
>>> youcould do
>>>
>>>> all the geometric operations you want, even spatial indexing, without
>>>
>>>
>>> really
>>>
>>>> having to worry about the non-standard sql portion of the database
>>>> (other than
>>>> writing the initial trigger function).
>>>
>>>
>>> Nice suggestion Chris! An often overlooked feature of the database.  Got
>>> any examples handy?  I'm using views like this when I really should 
>>> use a
>>> trigger.
> 
> 
>