POI table - postgresql - postgis - index - gist - trigger

gistfile1.sql

CREATE TABLE pois (
    id serial primary key,
    lon numeric(9,6),
    lat numeric(9,6),
    name character varying(255),
    geom geometry(Point,3857) -- EPSG:3857 is the spherical mercator projection
);
CREATE INDEX geom_idx ON pois USING GIST (geom GIST_GEOMETRY_OPS);

CREATE OR REPLACE FUNCTION update_geom() RETURNS "trigger" AS
$BODY$
    BEGIN
        IF NEW.lon IS NOT NULL AND NEW.lat IS NOT NULL THEN
            NEW.geom:=Transform(GeomFromEWKT('SRID=4326;POINT( ' || NEW.lon || ' ' || NEW.lat || ' )'), 3857); 
        ELSE
            NEW.geom:=null;
        END IF;
        RETURN NEW;
    END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_geom_poi BEFORE INSERT OR UPDATE ON pois FOR EACH ROW EXECUTE PROCEDURE update_geom();