back to first page ..
back to Recipes for EDA CookBook Eda
Help on Posgis
For our curernt version to create a database with postgis use
CREATE EXTENSION postgis
after the database is created
Postgis book http://www.postgis.fr/book/print/156#413413413
http://postgis.refractions.net/documentation/manual-1.5/
url: http://www.bostongis.com/?content_name=postgis_tut01#20
St_contains, St_Within, St_Intersect...: http://blogs.msdn.com/b/davidlean/archive/2008/10/24/sql-20008-spatial-samples-part-n-of-n-conditional-methods.aspx
http://postgis.refractions.net/download/postgis-1.5.2SVN.pdf
Projections
See in srid
srid System projection Country 3035 ETRS89 2154 RGF93_Lambert_93 France 27572 LambertII 27573 LambertIII(attention mapinfo vieille couche bien sale) 4326 WGS84 23030 ED50/ UTM zone 30N Spain 900913 27581 Lambert PROJCS["_MI_0",GEOGCS["FR-GRN",DATUM["D_FR-GRN"... 29903 TM65 Irish Grid Ireland 29901 OSNI 1952 / Irish National Grid Ireland 27700 OSGB 1936 / British National Grid Transverse Mercator UK 27572 NTF_France_II_degrees Lambert zone II France Sphere_ARC_INFO_Lambert_Azimuthal_Equal_Area 4258 http://www.opengis.net/def/crs/EPSG/0/4258 Spain 25830 subset of 4258 used for Spain Spain
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); --Another example update barlbr.t_ouvrage_ouv set the_geom=ST_PointFromText('POINT(216774.954951431 2341930.05473222)',27572) where ouv_id='EVEL-11'; --to generate this: select 'update barlbr.barlbr.t_ouvrage_ouv set the_geom=ST_PointFromText('''|| astext(op.the_geom)|| ''',2752) where ouv_id='''|| bo.ouv_id|| ''';' from barlbr.obstacles_prioritaires_bzh_2011 op join barlbr.t_ouvrage_ouv bo on id_ouvrage=ouv_id;
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 To figure out the srid of a data go to the table geometry_column, there is on line per table
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;
geometry columns
http://postgis.refractions.net/docs/ch04.html#spatial_ref_sys
4.3.3. Creating a Spatial Table
Creating a table with spatial data is done in two stages:
- Create a normal non-spatial table.
For example: CREATE TABLE ROADS_GEOM ( ID int4, NAME varchar(25) )
- Add a spatial column to the table using the OpenGIS AddGeometryColumn function.
The syntax is:
AddGeometryColumn( <schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension> )Or, using current schema:
AddGeometryColumn( <table_name>, <column_name>, <srid>, <type>, <dimension> ) Example1: SELECT AddGeometryColumn('public', 'roads_geom', 'geom', 423, 'LINESTRING', 2) Example2: SELECT AddGeometryColumn( 'roads_geom', 'geom', 423, 'LINESTRING', 2)
Here is an example of SQL used to create a table and add a spatial column (assuming that an SRID of 128 exists already):
CREATE TABLE parks ( park_id INTEGER, park_name VARCHAR, park_date DATE, park_type VARCHAR ); SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );
Here is another example, using the generic "geometry" type and the undefined SRID value of -1:
CREATE TABLE roads ( road_id INTEGER, road_name VARCHAR ); SELECT AddGeometryColumn( 'roads', 'roads_geom', -1, 'GEOMETRY', 3 );
4.3.4. Manually Registering Geometry Columns in geometry_columns
The AddGeometryColumn() approach creates a geometry column and also registers the new column in the geometry_columns table. If your software utilizes geometry_columns, then any geometry columns you need to query by must be registered in this table. Two of the cases where you want a geometry column to be registered in the geometry_columns table, but you can't use AddGeometryColumn, is in the case of SQL Views and bulk inserts. For these cases, you must register the column in the geometry_columns table manually. Below is a simple script to do that.
- Lets say you have a view created like this
CREATE VIEW public.vwmytablemercator AS SELECT gid, ST_Transform(the_geom,3395) As the_geom, f_name FROM public.mytable;
- To register this table in AddGeometry columns - do the following
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") SELECT '', 'public', 'vwmytablemercator', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM public.vwmytablemercator LIMIT 1;
- Lets say you created a derivative table by doing a bulk insert
SELECT poi.gid, poi.the_geom, citybounds.city_name INTO myschema.myspecialpois FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.the_geom, poi.the_geom);
- Create index on new table
CREATE INDEX idx_myschema_myspecialpois_geom_gist ON myschema.myspecialpois USING gist(the_geom);
- To manually register this new table's geometry column in geometry_columns we do the same thing as with view
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") SELECT '', 'myschema', 'myspecialpois', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM public.myschema.myspecialpois LIMIT 1;
Get SRID from a column
An example with ROE
select SRID (ref_position_locale) FROM geobs.obstacle_referentiel
Other usefull functions (memo)
select ST_SetSRID(). select Probe_Geometry_Columns() Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not already there. Also give stats on the number of inserts, already present, or possibly obsolete columns.
inserting a point from google map
update nat.t_station_sta set the_geom=st_transform(geomfromtext('POINT(' || long || ' ' || lat || ')', 4326),2154); -- beware google map gives lat and long in this order, you have to reverse --
