PostGIS Snippets: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
K (→‎Geometry Constructors)
 
(10 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 3: Zeile 3:
 
* [[PostGIS]]
 
* [[PostGIS]]
  
−
== Point Constructors ==
+
== Geometry Constructors ==
  
−
Point Constructors for geometry and geography types: Howto construct and print a coordinate in EPSG:4326 and in lon/lat format in either textual or 'symbolic' form?
+
Geometry constructors for geometry and geography types: Howto construct and print e.g. a coordinate in EPSG:4326 and in lon/lat format in either textual or 'symbolic' form?
  
−
* /*1 */ SELECT ST_AsEWKT(ST_GeomFromText('POINT(-71.06 42.28)', 4326)) -- ok, preferred if text input
+
Geometry:
−
* /*2 */ SELECT ST_AsEWKT(ST_GeomFromText('SRID=4326;POINT(-71.06 42.28)')) --  ok (if srid 'inline')
+
  /*1 */ '''SELECT ST_AsEWKT(ST_GeomFromText('POINT(-71.06 42.28)', 4326)) -- ok, preferred if text input'''
−
* /*3 */ SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ok, but why then ST_GeomFromText?
+
  /*2 */ '''SELECT ST_AsEWKT(ST_GeomFromText('SRID=4326;POINT(-71.06 42.28)')) --  ok (if srid 'inline')'''
−
* /*4 */ SELECT ST_AsEWKT('SRID=4326;POINT(-71.06 42.28)'::geometry) -- ok, but prefer 1/ST_GeomFromText
+
  /*3 */ SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ok, but why then ST_GeomFromText?
−
* /*5 */ SELECT ST_AsEWKT(ST_SetSRID('POINT(-71.06 42.28)'::geometry, 4326)) -- ok, but prefer 1/ST_GeomFromText
+
  /*4 */ SELECT ST_AsEWKT('SRID=4326;POINT(-71.06 42.28)'::geometry) -- ok, but prefer 1/ST_GeomFromText
−
* /*6 */ SELECT ST_AsEWKT(ST_MakePoint(-71.06, 42.28, 4326)) -- ok for 'symbolic' form. But why no EWKT returning?
+
  /*5 */ SELECT ST_AsEWKT(ST_SetSRID('POINT(-71.06 42.28)'::geometry, 4326)) -- ok, but prefer 1/ST_GeomFromText
 +
  /*6 */ SELECT ST_AsEWKT(ST_MakePoint(-71.06, 42.28, 4326)) -- ok for 'symbolic' form. But why no EWKT returning? ST_Point is OGC alias for ST_MakePoint.
  
−
* /*1 */ SELECT ST_AsEWKT(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) -- ERROR: unknown ST_GeogFromText(): why?
+
Geography:
−
* /*2a*/ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_AsEWKT: why not?
+
  /*1 */ SELECT ST_AsEWKT(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) -- ERROR: unknown ST_GeogFromText()
−
* /*2b*/ SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ok (if srid 'inline'); no AsEWKT  
+
  /*2a*/ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_AsEWKT. 
−
* /*3 */ SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_GeogFromEWKT: why?
+
  /*2b*/ '''SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ok (if srid 'inline'); (there's no AsEWKT)'''
−
* /*4 */ SELECT ST_AsText('SRID=4326;POINT(-71.06 42.28)'::geography) -- ok (no AsEWKT), but prefer 1/ST_GeomFromText
+
  /*3 */ SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_GeogFromEWKT.
−
* /*5 */ SELECT ST_AsText(ST_SetSRID('POINT(-71.06 42.28)'::geography, 4326)) -- ERROR: ok SetSRID w. geography unnecessary
+
  /*4 */ '''SELECT ST_AsText('SRID=4326;POINT(-71.06 42.28)'::geography) -- ok (no AsEWKT)'''
−
* /*6 */ Equivalent to ST_MakePoint for geography types missing: => ST_MakePointG?
+
  /*5 */ SELECT ST_AsText(ST_SetSRID('POINT(-71.06 42.28)'::geography, 4326)) -- ERROR: ok SetSRID w. geography unnecessary
 +
  /*6 */ Equivalent to ST_MakePoint for geography types missing: => ST_MakePointG?
  
−
  => Create ST_AsEWKT for geography and harmonize ST_AsText (depreciate with EWKT).
+
Wishlist for future(!) PostGIS developments:
−
  => Create ST_GeogFromEWKT for geography and harmonize ST_GeogFromText (depreciate with EWKT).
+
* Create ST_AsEWKT for geography and harmonize ST_AsText (depreciate with EWKT).
 +
* Create ST_GeogFromEWKT for geography and harmonize ST_GeogFromText (depreciate with EWKT).
 +
 
 +
== Bounding Box (bbox, box, mbr, ...) Constructors ==
 +
 
 +
-- Works:
 +
  SELECT ST_AsText(ST_SetSRID('BOX3D(572126 5671279,1258837 6154972)'::box3d, 900913))
 +
  SELECT ST_AsText(ST_SetSRID('BOX(572126 5671279,1258837 6154972)'::box2d, 900913))
 +
  SELECT ST_AsText(ST_SetSRID(ST_MakeBox2D(ST_Point(572126, 5671279), ST_Point(1258837, 6154972)), 900913))
 +
  SELECT ST_AsText(ST_SetSRID(ST_MakeBox3D(ST_Point(572126, 5671279), ST_Point(1258837, 6154972)), 900913))
 +
 
 +
-- ...does not work:
 +
  SELECT ST_AsText(SetSRID('BOX2D(572126 5671279,1258837 6154972)'::box2d, 900913))
 +
  SELECT ST_AsText(ST_GeomFromText('BOX2D(572126 5671279,1258837 6154972)', 900913))
 +
  SELECT ST_AsText(ST_GeomFromText('BOX3D(572126 5671279,1258837 6154972)', 900913))
  
  
 
== hstore ==
 
== hstore ==
  
−
Key-Value data type.
+
Key-Value data type of PostgreSQL.
  
 
=== Index ===
 
=== Index ===
  
 +
Create index for a field of type hstore:
 
   DROP INDEX planet_osm_point_tags;
 
   DROP INDEX planet_osm_point_tags;
 
   CREATE INDEX planet_osm_point_tags  
 
   CREATE INDEX planet_osm_point_tags  
Zeile 37: Zeile 54:
 
     USING gin (tags)
 
     USING gin (tags)
  
 +
Create a index with expression:
 
   DROP INDEX planet_osm_point_tags2;
 
   DROP INDEX planet_osm_point_tags2;
 
   CREATE INDEX planet_osm_point_tags2  
 
   CREATE INDEX planet_osm_point_tags2  
Zeile 52: Zeile 70:
 
   "italian";197
 
   "italian";197
 
   "pizza";123
 
   "pizza";123
−
   "burger";112
+
    
−
 
 
−
 
 
 
   SELECT count(*) AS c, cuisine FROM (
 
   SELECT count(*) AS c, cuisine FROM (
 
     SELECT lower(tags->'cuisine') AS cuisine FROM planet_osm_point WHERE tags ? 'cuisine'
 
     SELECT lower(tags->'cuisine') AS cuisine FROM planet_osm_point WHERE tags ? 'cuisine'

Aktuelle Version vom 6. Dezember 2016, 02:18 Uhr

See also:

Geometry Constructors

Geometry constructors for geometry and geography types: Howto construct and print e.g. a coordinate in EPSG:4326 and in lon/lat format in either textual or 'symbolic' form?

Geometry: 

 /*1 */ SELECT ST_AsEWKT(ST_GeomFromText('POINT(-71.06 42.28)', 4326)) -- ok, preferred if text input
 /*2 */ SELECT ST_AsEWKT(ST_GeomFromText('SRID=4326;POINT(-71.06 42.28)')) --  ok (if srid 'inline')
 /*3 */ SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ok, but why then ST_GeomFromText?
 /*4 */ SELECT ST_AsEWKT('SRID=4326;POINT(-71.06 42.28)'::geometry) -- ok, but prefer 1/ST_GeomFromText
 /*5 */ SELECT ST_AsEWKT(ST_SetSRID('POINT(-71.06 42.28)'::geometry, 4326)) -- ok, but prefer 1/ST_GeomFromText
 /*6 */ SELECT ST_AsEWKT(ST_MakePoint(-71.06, 42.28, 4326)) -- ok for 'symbolic' form. But why no EWKT returning? ST_Point is OGC alias for ST_MakePoint.

Geography: 

 /*1 */ SELECT ST_AsEWKT(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) -- ERROR: unknown ST_GeogFromText()
 /*2a*/ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_AsEWKT.  
 /*2b*/ SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ok (if srid 'inline'); (there's no AsEWKT)
 /*3 */ SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_GeogFromEWKT. 
 /*4 */ SELECT ST_AsText('SRID=4326;POINT(-71.06 42.28)'::geography) -- ok (no AsEWKT)
 /*5 */ SELECT ST_AsText(ST_SetSRID('POINT(-71.06 42.28)'::geography, 4326)) -- ERROR: ok SetSRID w. geography unnecessary
 /*6 */ Equivalent to ST_MakePoint for geography types missing: => ST_MakePointG?

Wishlist for future(!) PostGIS developments:

  • Create ST_AsEWKT for geography and harmonize ST_AsText (depreciate with EWKT).
  • Create ST_GeogFromEWKT for geography and harmonize ST_GeogFromText (depreciate with EWKT).

Bounding Box (bbox, box, mbr, ...) Constructors

-- Works: 

 SELECT ST_AsText(ST_SetSRID('BOX3D(572126 5671279,1258837 6154972)'::box3d, 900913))
 SELECT ST_AsText(ST_SetSRID('BOX(572126 5671279,1258837 6154972)'::box2d, 900913))
 SELECT ST_AsText(ST_SetSRID(ST_MakeBox2D(ST_Point(572126, 5671279), ST_Point(1258837, 6154972)), 900913))
 SELECT ST_AsText(ST_SetSRID(ST_MakeBox3D(ST_Point(572126, 5671279), ST_Point(1258837, 6154972)), 900913))

-- ...does not work: 

 SELECT ST_AsText(SetSRID('BOX2D(572126 5671279,1258837 6154972)'::box2d, 900913))
 SELECT ST_AsText(ST_GeomFromText('BOX2D(572126 5671279,1258837 6154972)', 900913))
 SELECT ST_AsText(ST_GeomFromText('BOX3D(572126 5671279,1258837 6154972)', 900913))


hstore

Key-Value data type of PostgreSQL.

Index

Create index for a field of type hstore: 

 DROP INDEX planet_osm_point_tags;
 CREATE INDEX planet_osm_point_tags 
   ON planet_osm_point 
   USING gin (tags)

Create a index with expression: 

 DROP INDEX planet_osm_point_tags2;
 CREATE INDEX planet_osm_point_tags2 
   ON planet_osm_point 
   USING btree (tags)
   WHERE tags->'natural'='peak';

Statistics: Counting the different values in hstore

 SELECT lower(tags->'cuisine') as tags, count(*) AS count 
 FROM planet_osm_point WHERE tags ? 'cuisine'
 GROUP BY 1
 ORDER BY 2 DESC;
 "regional";416
 "italian";197
 "pizza";123
 
 SELECT count(*) AS c, cuisine FROM (
   SELECT lower(tags->'cuisine') AS cuisine FROM planet_osm_point WHERE tags ? 'cuisine'
   UNION ALL
   SELECT lower(tags->'cuisine') AS cuisine FROM planet_osm_polygon WHERE tags ? 'cuisine'
 ) AS t GROUP BY cuisine ORDER BY c DESC;
Von «https://giswiki.hsr.ch/index.php?title=PostGIS_Snippets&oldid=59260»