PostGIS Snippets: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
Zeile 2: Zeile 2:
 
* [[PostGIS - Tipps und Tricks]]
 
* [[PostGIS - Tipps und Tricks]]
 
* [[PostGIS]]
 
* [[PostGIS]]
 +
 +
== Point 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?
 +
 +
* /*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?
 +
 +
* /*1 */ SELECT ST_AsEWKT(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) -- ERROR: unknown ST_GeogFromText(): why?
 +
* /*2a*/ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_AsEWKT: why not?
 +
* /*2b*/ SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ok (if srid 'inline'); no AsEWKT
 +
* /*3 */ SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_GeogFromEWKT: why?
 +
* /*4 */ SELECT ST_AsText('SRID=4326;POINT(-71.06 42.28)'::geography) -- ok (no AsEWKT), but prefer 1/ST_GeomFromText
 +
* /*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).
 +
  => Create ST_GeogFromEWKT for geography and harmonize ST_GeogFromText (depreciate with EWKT).
 +
 +
 +
== hstore ==
 +
 +
Key-Value data type.
 +
 +
=== Index ===
 +
 +
  DROP INDEX planet_osm_point_tags;
 +
  CREATE INDEX planet_osm_point_tags
 +
    ON planet_osm_point
 +
    USING gin (tags)
 +
 +
  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
 +
  "burger";112
 +
 +
 +
  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;

Version vom 16. Februar 2011, 15:14 Uhr

See also:

Point 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?

  • /*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?
  • /*1 */ SELECT ST_AsEWKT(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) -- ERROR: unknown ST_GeogFromText(): why?
  • /*2a*/ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_AsEWKT: why not?
  • /*2b*/ SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)')) -- ok (if srid 'inline'); no AsEWKT
  • /*3 */ SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)')) -- ERROR: no ST_GeogFromEWKT: why?
  • /*4 */ SELECT ST_AsText('SRID=4326;POINT(-71.06 42.28)'::geography) -- ok (no AsEWKT), but prefer 1/ST_GeomFromText
  • /*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).
 => Create ST_GeogFromEWKT for geography and harmonize ST_GeogFromText (depreciate with EWKT).


hstore

Key-Value data type.

Index

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

 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
 "burger";112



 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=31977»