a little of postgis

What I am going to show you is how from a geographical table (postgis) of segments of streets we are going to put together a table with the intersections of those streets. Surely they are already asking the same question that I asked Fernando, what for? That is, to insert redundancy in the system, and the answer is very simple, performance. While I do not think anyone has this problem on time is a good example of how to play with postgis.So, well, here we go, the first thing I show you is the tables we will use:

geoprop=# \d segmentos_calle;
Table "public.segmentos_calle"
Column | Type | Modifiers
------------------+----------------------+----------------------------------------------------------------------------
idsegmentoscalle | integer | not null default nextval('segmentos_calle_idsegmentoscalle_seq'::regclass)
idcalles | integer | not null
alturadesdeizq | smallint |
alturahastaizq | smallint |
alturadesdeder | smallint |
alturahastader | smallint |
anchoacera | real |
anchovereda | real |
cpa | character varying(7) |
the_geom | geometry |
Indexes:
"segmentos_calle_idsegmentocalle" PRIMARY KEY, btree (idsegmentoscalle)
"geomindex" gist (the_geom)
"segmentos_calle_callessegmento_calle" btree (idcalles)
"segmentos_calle_idcalles" btree (idcalles)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
Foreign-key constraints:
"callessegmento_calle" FOREIGN KEY (idcalles) REFERENCES calles(idcalles) ON UPDATE CASCADE ON DELETE CASCADE
geoprop=# \d barrios;
Table "public.barrios"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------------------------
gid | integer | not null default nextval('barrios_gid_seq'::regclass)
id | bigint |
nombre | character varying(20) |
area | double precision |
perimetro | double precision |
the_geom | geometry |
Indexes:
"barrios_pkey" PRIMARY KEY, btree (gid)
"barrios_the_geom_gist" gist (the_geom)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)

That is, we have two tables, one with the segments of streets, and another with the neighborhoods. The objective is to create a third table with the ids of the segments that are interchanged in each neighborhood, bone, idsegment1, idsegment2 and idbarrio.

First of all we created the table:

CREATE TABLE intersecciones
(
idsegmentoscalle1 integer NOT NULL,
idsegmentoscalle2 integer NOT NULL,
id_barrio bigint NOT NULL,
CONSTRAINT "intersec_PK" PRIMARY KEY (idsegmentoscalle1, idsegmentoscalle2, id_barrio)
)
WITH (
OIDS = TRUE
)
;
ALTER TABLE intersecciones OWNER TO devgeoprop;

Well, now we need to think about what we have to get.
1) Get the list of segment ides that intersect
2) get the point where they do it
3) get the neighborhood where they do it
Go for it!
We are going to create two functions to avoid making anonymous selects that in the end complicate the reading:

1) getbarriofromgem(the_geom)
Returns the neighborhood id of a given geometry
2) segmentosinter (idsegmento1, idsegmento2)
Returns the geometry of the intersection of two streets.

CREATE OR REPLACE FUNCTION getbarriofromgem(geometry) returns bigint AS 'select id from barrios where ST_Contains(the_geom,$1);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE or REPLACe FUNCTION segmentosinter(integer, integer) returns geometry AS 'select distinct Intersection(calle1.the_geom,calle2.the_geom) from segmentos_calle calle1, segmentos_calle calle2 where ST_Touches(calle1.the_geom, calle2.the_geom) and calle1.idsegmentoscalle = $2 and calle2.idsegmentoscalle = $1;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

And now only remains to do our simple select:

insert into intersecciones (idsegmentoscalle1, idsegmentoscalle2, id_barrio) select distinct calle1.idsegmentoscalle, calle2.idsegmentoscalle, getbarriofromgem(segmentosinter(calle1.idsegmentoscalle, calle2.idsegmentoscalle)) from segmentos_calle calle1, segmentos_calle calle2 where calle1.the_geom && calle2.the_geom and calle1.idcalles <> calle2.idcalles;

Simpler impossible, this can be optimized if it complicates the select, putting the functions inside. But, it is something that is done only once to load the table, therefore I prefer it to read well;).

Now the table is loaded: D, but .. what do you do if you delete a street segment? Well, just add a constraint:

ALTER TABLE intersecciones ADD CONSTRAINT "idsegmentos1_FK" FOREIGN KEY (idsegmentoscalle1) REFERENCES segmentos_calle (idsegmentoscalle) ON DELETE CASCADE;
ALTER TABLE intersecciones ADD CONSTRAINT "idsegmentos2_FK" FOREIGN KEY (idsegmentoscalle2) REFERENCES segmentos_calle (idsegmentoscalle) ON DELETE CASCADE;

Now, what happens if you insert a new segment?
Well, for that we are going to create a trigger:

create or replace function actualiza_segmentoscalle() returns trigger as $emp_stamp$
begin
insert into intersecciones (idsegmentoscalle1, idsegmentoscalle2, id_barrio) select distinct calle1.idsegmentoscalle, calle2.idsegmentoscalle, getbarriofromgem(segmentosinter(calle1.idsegmentoscalle, calle2.idsegmentoscalle)) from segmentos_calle calle1, segmentos_calle calle2 where calle1.the_geom && calle2.the_geom and calle1.idcalles <> calle2.idcalles and calle1.idsegmentoscalle = NEW.idsegmentoscalle;
return NEW;
end;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER actualiza_segmentos_calle BEFORE INSERT ON segmentos_calle
FOR EACH ROW EXECUTE PROCEDURE actualiza_segmentoscalle();

With this we make sure that when you insert a new segment the intersections are updated as well.

I hope you serve them, take a look at the geoprop project, maybe by the time you read this article will be finished.

If you need a postgres hosting with mapserver, geoserver and postgis support I recommend Root Solutions .

Greetings.

Leave a Reply