[Pgday.Seoul 2017] 1. PostGIS? ??? ? PostgreSQL ?? - ???
2 likes2,450 views
PostGIS? ??? ? PostgreSQL ?? ???
![PgDay Seoul 2017
IT? ?? JSON? XML? ??
33
SELECT ST_AsGeoJSON(geom) FROM floodplain_city;
SELECT ST_AsGML(geom) FROM floodplain_city;
{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [127.43 37.32]
},
"properties": {
"name": "Dinagat Islands"
}
}
<Feature>
<name>Dinagat Islands</name>
<position>
<gml:Point srsDimension="2"
srsName="http://www.opengis.net/def/crs/EPSG/0/
4326">
<gml:pos>127.43 37.32</gml:pos>
</gml:Point>
</position>
</Feature>](https://image.slidesharecdn.com/postgispostgresqlpgday2017-171103060406-171106044046/85/Pgday-Seoul-2017-1-PostGIS-PostgreSQL-33-320.jpg)
[Pgday.Seoul 2017] 1. PostGIS? ??? ? PostgreSQL ?? - ???
- 1. PostGIS? ??? ? PostgreSQL ?? 2017. 11. 4 ??? (jangbi882@gmail.com)
- 2. Spatial Extension. Why? How?
- 3. PgDay Seoul 2017 ?? ???? Spatial SQL 3 SELECT superhero.name FROM city, superhero WHERE ST_Contains(city.geom, superhero.geom) AND city.name = 'Gotham'; ? city? superhero ??? ???? JOIN ??? Field? ??. ? ???, ｀??¨ ? ?? ???? ?? ? ??! ? ??? ???? ??? ??? JOIN? ? ? ??. ??: http://postgis.net/
- 4. PgDay Seoul 2017 ???? ??? ? 1) ?? 3??????? ??? ???? ???? 2) ? ????? 3??????? ????? ????? ??? 3) ? ???? ??????? ? ???? ???? ?? ?? 4) ? ????? ??? ?? ??? 5) ??? ???? 3????? ??? ??? ????? ???? 4 ??: ????? ???, ??? ??? ???? ??
- 5. PgDay Seoul 2017 Spatial SQL ??? ? ???? ?? ??? ??? ? ?? ?? SQL??. ? ????(Geometry/Geography/Raster)? BLOB?? ????. ? ?? ???? database? ???? ??(??)? ???. ? ???? database?? ?? ??(??)?? ???? ????. ??: PostGIS for Managers, Paul Ramsey http://s3.cleverelephant.ca/2014-postgis-for-managers.pdf
- 6. PgDay Seoul 2017 Spatial SQL? ?? 6 ? ＾GIS in SQL￣ Cdatabase ??? ??? ??? ???? ???? ?? ? Shared Editing C?? ??? ??? ???? ??? ?? ? Performance and Scale C??? ???? ?? ???? ?? C???? GIS?? ??? ??? ??/?? ??? ???
- 7. PgDay Seoul 2017 Spatial Database ???? 7 ?Spatial data types CGeometry(point, line, polygon ?) ? Spatial indexing C????? ??? ?? ?? ? Spatial functions, C??? ??? ?? ?? C???? ???? ?? ??: http://www.opengeospatial.org/standards/sfs
- 8. PgDay Seoul 2017 Spatial Data Type 8 ??: https://en.wikipedia.org/wiki/Well-known_text
- 9. PgDay Seoul 2017 Spatial Index 9
- 10. PgDay Seoul 2017 Spatial Function 10 ? ST_Length ? ST_Area ? ST_Distance ? ST_Buffer ? ST_Scale ? ST_Rotate ? ST_Transform ? ST_Intersection ? ST_Union ? ST_Simpllify ? ST_Convexhull ? ST_AsText ? ST_AsBinary ? ST_FromText ? A && B ? A @ B ? A ~ B ? A <-> B ? A <#> B ? A |=| B ? A <=> B??: http://postgis.net/docs/manual-2.3/reference.html#Operators
- 11. PgDay Seoul 2017 ????? ???? Spatial DBMS ?? ??: /gis_todd/postgis-and-spatial-sql PostGISSpatialLite
- 12. PgDay Seoul 2017 PostGIS Archtecture 12 ??: /SimeonNedkov/delft-postgis PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom GDAL
- 13. PgDay Seoul 2017 PostGIS Archtecture 13 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom PostgreSQL? ?? RDBMS? ??? ORDBMS?. GDAL ??: /SimeonNedkov/delft-postgis
- 14. PgDay Seoul 2017 PostGIS Archtecture 14 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom PostGIS? ORDBMS? ??? ??? Native ???? Spatial SQL? ??? Type, Index, Function ?? ????. GDAL ??: /SimeonNedkov/delft-postgis
- 15. PgDay Seoul 2017 PostGIS Archtecture 15 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom Database? ?? ??? ? ?? ?? ???? Sub Library ?? Low Level? ??? ??? ???. GDAL ??: /SimeonNedkov/delft-postgis
- 16. PgDay Seoul 2017 PostGIS Archtecture 16 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom ?? ???? C++?? ???? ?????? ??? ?? ??? ??? ??/?? ??? ????. GDAL ??: /SimeonNedkov/delft-postgis
- 17. PgDay Seoul 2017 PostGIS Archtecture 17 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom ??? ?? ?????? ?? ??? ?? ? ??? ???? ????. GDAL ??: /SimeonNedkov/delft-postgis
- 18. PgDay Seoul 2017 PostGIS Archtecture 18 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom XML ?????? ??? GML, KML ? XML ?? ???? ??? ???. GDAL ??: /SimeonNedkov/delft-postgis
- 19. PgDay Seoul 2017 PostGIS Archtecture 19 PostgreSQL PostGIS Extension GEOS Proj4 LibXML2 LibLwGeom ?? ???? C++?? Raster GIS ?????? ??? Raster ??? ??? ??? ???? ??. GDAL
- 21. PgDay Seoul 2017 ?? SQL ???~ 21 SELECT * FROM liquor_licenses; ??: /gis_todd/postgis-and-spatial-sql
- 22. PgDay Seoul 2017 ???? ?? ???. 22 SELECT * FROM liquor_licenses WHERE license_ty='Tavern';
- 23. PgDay Seoul 2017 ??? ????? 23 SELECT COUNT(establish) FROM liquor_licenses WHERE license_ty='Brew Pub';
- 24. PgDay Seoul 2017 ???? ??? ? ???. 24 SELECT AVG(char_length(establish)), license_ty FROM liquor_licenses GROUP BY license_ty;
- 25. PgDay Seoul 2017 ???? ?? ?? ??? ? ? ???. 25 SELECT AVG(char_length(establish)), STDDEV(char_length(establish)), license_ty FROM liquor_licenses GROUP BY license_ty;
- 26. PgDay Seoul 2017 ??? ?? ?? 26 SELECT ST_AsText(geom) FROM liquor_licenses; POINT (127.43 37.32)
- 27. PgDay Seoul 2017 ??? ??? ?? ????~~~ 27 SELECT ST_LENGTH (geom) , strname FROM street_centerlines;
- 28. PgDay Seoul 2017 ?! ??? ???? ??? ???? ?.? 28 ?? ?? ??? 1?? ???~~~~
- 29. PgDay Seoul 2017 ?? ??? ???? ???? ???. 29 SELECT ST_LENGTH(ST_TRANSFORM(geom,5179)), strname FROM street_centerlines; ? EPSG:5179 - ???? TM ??? - ??????? ??, ? ?????? ??? - ?? ?? ??? ??
- 30. PgDay Seoul 2017 ?? ??? ??? ? ???. 30 SELECT objectid, ST_AREA(ST_TRANSFORM(geom,5179)) FROM buildings;
- 31. PgDay Seoul 2017 ?´ ?´ ??? ???? C ?? ?? ?? 31 ALTER TABLE liquor_licenses ADD Column buffer geometry(Polygon,4326) ? EPSG:4326 - ??? ??? ??? - GPS?? ?? ???? ?? - ???? ?? ??? ? ? ?? ??
- 32. PgDay Seoul 2017 ?´ ?´ ??? ???? C ?? ??? ?? 32 UPDATE liquor_licenses SET Buffer= ST_TRANSFORM (ST_BUFFER(ST_TRANSFORM(geom,5179),30),4326);
- 33. PgDay Seoul 2017 IT? ?? JSON? XML? ?? 33 SELECT ST_AsGeoJSON(geom) FROM floodplain_city; SELECT ST_AsGML(geom) FROM floodplain_city; { "type": "Feature", "geometry": { "type": "Point", "coordinates": [127.43 37.32] }, "properties": { "name": "Dinagat Islands" } } <Feature> <name>Dinagat Islands</name> <position> <gml:Point srsDimension="2" srsName="http://www.opengis.net/def/crs/EPSG/0/ 4326"> <gml:pos>127.43 37.32</gml:pos> </gml:Point> </position> </Feature>
- 34. PgDay Seoul 2017 ?? ? ? ??? ????? ???~ 34 SELECT ST_NPoints(geom) FROM street_centerlines;
- 35. PgDay Seoul 2017 ????? ??? ?? ??? JOIN?~ 35 SELECT a.strname,a.gid,a.geom, a.leftadd1,a.leftadd2,a.rgtadd1,a.rgtadd2 FROM street_centerlines a, floodplain_city b WHERE ST_Crosses(a.geom,b.geom);
- 36. PgDay Seoul 2017 ? ??(??) ???? ?????~ 36 SELECT a.establish, a.license_ty,a.gid,a.geom FROM liquor_licenses a, zoning b WHERE st_within(a.geom, b.geom) AND zone_name='LOW DENSITY MIXED-USE NEIGHBORHOOD DISTRICT';
- 37. PgDay Seoul 2017 ??? ???? ?? 37 SELECT COUNT(a.establish), a.license_ty, b.zone_name ,a.gid, a.geom FROM liquor_licenses a, zoning b WHERE ST_WITHIN(a.geom,b.geom) GROUP BY b.zone_name, a.license_ty;
- 38. ????? ?????~ 38