PostGIS Snippets: Unterschied zwischen den Versionen
Aus Geoinformation HSR
Stefan (Diskussion | Beiträge) |
Stefan (Diskussion | Beiträge) |
||
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:
Inhaltsverzeichnis
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;