gis:postgresql-postgis-notes
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
gis:postgresql-postgis-notes [2022/03/18 18:00] Ilias Iliopoulos |
gis:postgresql-postgis-notes [2024/02/02 21:49] (current) Ilias Iliopoulos |
||
|---|---|---|---|
| Line 13: | Line 13: | ||
| When you use database columns where geometry data will be stored, do not create those columns using the normal database commands. **Use the PostGIS commands in order to specify the geometry parameters.** | When you use database columns where geometry data will be stored, do not create those columns using the normal database commands. **Use the PostGIS commands in order to specify the geometry parameters.** | ||
| - | Examples: Create a 3D point column in EPSG:4326 and a 2D column in GGRS87. You need to specify the database schema, the table, the SRID, the type of layer (points, linestrings, polygons, etc) and the dimensions (2 for x,y and 3 for x,y,z). | + | Examples: Create a 3D point column in EPSG:4326 and a 2D column in GGRS87. You need to specify the database schema ''public'', the table ''nodes'' and ''nodes2'', the geometry columns ''geom'' in both tables, the SRID ''4326'' and ''2100'', the type of layer (points, linestrings, polygons, etc) and the dimensions (2 for x,y and 3 for x,y,z). |
| <code> | <code> | ||
| Line 35: | Line 35: | ||
| ===== Insert a point with geometry ===== | ===== Insert a point with geometry ===== | ||
| - | Example: Insert a point in a table with a primary key called ''id'' and | + | Example: Insert a point in a table with a primary key called ''id'', geometry column ''geom'' and coordinates longitude 23.18 and latitude 38.13 |
| <code> | <code> | ||
| Line 74: | Line 74: | ||
| ===== Correct a geom after modifying lat, lon ===== | ===== Correct a geom after modifying lat, lon ===== | ||
| - | Suppose you made a correction to the numeric values of lat and lon of London and you want to update this single database row. | + | Suppose you made a correction to the numeric values of lat and lon of London and you want to update the geom column of this single database row. |
| <code> | <code> | ||
| Line 83: | Line 83: | ||
| Suppose that you want to retrieve the 5 nearest neighbours of point (22.164885,38.924171). The fastest and most effective way is shown below, using the ''<->'' operator, if supported by your versions. | Suppose that you want to retrieve the 5 nearest neighbours of point (22.164885,38.924171). The fastest and most effective way is shown below, using the ''<->'' operator, if supported by your versions. | ||
| - | The data are in EPSG:4326 but it requires the ''ST_SetSRID'' to make sure that both sides are in the same SRID. | + | The data are in EPSG:4326 but it requires the ''ST_SetSRID'' to make sure that both sides of the ''<->'' operator are in the same SRID. |
| I set both sides to EPSG:2100 because I want to know the distance in meters. Otherwise, I could avoid ST_Transform. | I set both sides to EPSG:2100 because I want to know the distance in meters. Otherwise, I could avoid ST_Transform. | ||
| Line 94: | Line 94: | ||
| LIMIT 5 | LIMIT 5 | ||
| </code> | </code> | ||
| + | |||
| + | ===== Identify the points of a Point layer that are outside polygons of a Polygon layer ===== | ||
| + | |||
| + | There are multiple queries that you can use to solve this problem. You can find an interesting discussion in [[https://gis.stackexchange.com/questions/376234/find-polygons-that-has-no-points-in-postgis|Find polygons that has no points in PostGIS]]. For big data sets, I have found that the query that executes in the shortest time is: | ||
| + | |||
| + | <code> | ||
| + | SELECT polygons.id FROM polygons WHERE NOT EXISTS | ||
| + | (SELECT 1 FROM points WHERE ST_Intersects(polygons.geom, points.geom)) | ||
| + | </code> | ||
| + | |||
| + | For comparison purposes, in a data set with 45.000 points and 45.000 buildings, this query took approximately 4 minutes, while other queries took more than 14 minutes! | ||
| + | |||
| + | **WARNING:** You need to pay particular attention on how much is exactly "outside". You may encounter cases where the point is considered to be at exactly the perimeter of the polygon, because it has been "snapped" at the side of the polygon, using the snapping features of your GIS software. Yet, there is always a tolerance in snapping and the point might be geometrically within a few tenths of a millimeter inside or outside the polygon. A very small buffer area should be considered around the point to account for this ambiguity. | ||
| ===== Import an SHP file manually ===== | ===== Import an SHP file manually ===== | ||
| Line 123: | Line 136: | ||
| </code> | </code> | ||
| + | |||
| + | ~~DISQUS~~ | ||
gis/postgresql-postgis-notes.1647619248.txt.gz ยท Last modified: 2022/03/18 18:00 by Ilias Iliopoulos