Help on Posgis

back to Recipes for EDA CookBook Eda

Postgis book ​http://www.postgis.fr/book/print/156#413413413

I would like to create a postgis table from a postgres table with x y

The data are in lambert II étendu

select * from bdmap.spatial_ref_sys where srtext like '%Lambert%' and srtext like '%II%' 

seems to be 27572

SELECT AddGeometryColumn('bdmap', 'station','the_geom', 27572,'POINT',2) 

from Laurent

UPDATE bdmap
   SET the_geom=PointFromText('POINT(' || st_abcisse || ' ' || st_ordonnee || ')',27572);

I've got a message from Qgis saying that I'm not allowed to use a table without int4 primary key or OID

ALTER TABLE bdmap.station SET WITH OIDS;

Here we add a validity constraint

ALTER TABLE bdmap.station ADD CONSTRAINT geometry_valid_check CHECK (isvalid(the_geom)); 

To transform the coordinate towards the CCM

SELECT ST_Transform(the_geom,3035) FROM bdmap.station;

Building an index (very quick with this table)

CREATE INDEX indexStations ON bdmap.station
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 

​http://www.postgis.fr/documentation/debian/postgis-1.1.2/html/ch02.html#id2499646 Usefull functions in postgis, they take as argument a geom

• Area2d()
• geometrytype()
• Distance(A,B)
• WithIn(A,B) A inside B
• Min()
• Distance()

Reprojecting / snapping point-geometries onto a given line-geometry using PostGIS ​http://62.160.92.241:8066/trac/wiki/CookBook%20Postgis?action=edit

SELECT ST_Line_Interpolate_Point(
		line_to_project_onto.the_geom,
		ST_Line_Locate_Point(
			line_to_project_onto.the_geom,
			points_to_project_onto_line.the_geom
		)
	)
FROM line_to_project_onto, points_to_project_onto_line;

which translates into

{{{
SELECT ST_Line_Interpolate_Point(
		riversegments.the_geom,
		ST_Line_Locate_Point(
			riversegments.the_geom,
			stationsp2.the_geom
		)
	)
FROM stationsp2, riversegments;
}}}