back to first page [..][[BR]]
back to Recipes for EDA [wiki:"CookBook Eda"]


= Help on Posgis =

For our curernt version to create a database with postgis use

{{{#!sql
CREATE EXTENSION postgis
}}}

after the database is created

Postgis book http://www.postgis.fr/book/print/156#413413413 [[BR]]
http://postgis.refractions.net/documentation/manual-1.5/ [[BR]]
url: http://www.bostongis.com/?content_name=postgis_tut01#20  [[BR]]
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 [[BR]]
http://postgis.refractions.net/download/postgis-1.5.2SVN.pdf [[BR]]

= 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||google|| 
 ||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 '''

{{{
#!sql
select * from bdmap.spatial_ref_sys where srtext like '%Lambert%' and srtext like '%II%' 
}}}
seems to be 27572
{{{
#!sql
SELECT AddGeometryColumn('bdmap', 'station','the_geom', 27572,'POINT',2) 
}}}
from Laurent
{{{
#!sql
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 ''
{{{
#!sql
ALTER TABLE bdmap.station SET WITH OIDS;
}}}
Here we add a validity constraint
{{{
#!sql
ALTER TABLE bdmap.station ADD CONSTRAINT geometry_valid_check CHECK (isvalid(the_geom)); 
}}}
To transform the coordinate towards the CCM[[CC]]
To figure out the srid of a data go to the table geometry_column, there is on line per table
{{{ 
#!sql
SELECT ST_Transform(the_geom,3035) FROM bdmap.station;
}}}
Building an index (very quick with this table)
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
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.

{{{
#!sql   
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):
{{{
#!sql
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:
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
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
{{{
#!sql
select SRID (ref_position_locale) FROM geobs.obstacle_referentiel
}}}	
== Other usefull functions (memo) ==
{{{
#!sql
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 ==
{{{
#!sql 
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 --