Hot answers tagged

11

OK. I figured it out myself. By doing a ST_ShortestLine() on each vertex of the polygon and then a ST_Convexhull() on this set of lines and a final ST_Difference() between that area and the area of the polygon I will get what I need.


10

Fortunately, with the prio dependency, this is easier to solve than a purely recursive difference aggregation. I'd prefer the LATERAL statement (here in full verbose mode), both for clarity of the statement and performance SELECT ST_Multi(COALESCE( ST_Difference(a.geom, blade.geom), a.geom )) AS geom FROM table1 AS a CROSS JOIN ...


10

If your point is located here: it's encoded as WKB which stands for Well Known Binary. The exact same point, expressed as WKT (for Well Know Text, which is more human readable) is: 'POINT (30.3504317999999991 50.4503695000000008)' Please see https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry and https://portal.ogc.org/files/?...


10

I'll answer my own question but credit goes to user30184 for giving me a couple clues. The problem seems to be that the first few thousand rows in my table are LineStrings and apparently QGIS isn't examining all of the rows and finding the other geometry types in the table. The solution, at least in QGIS 3.18.2, is to check the "Don't resolve type of ...


10

True Polygon containment is a costly operation; not only does a containment check have to run intersection computations between each pair of vertices in one polygon for each pair of vertices in the other, but is it also impossible to improve by subdividing any of them. That being said, your query is also not going to return the desired result; you are ...


9

Use UNION or UNION ALL, depending on whether you want duplicates. Example: SELECT f1,f2,f3 FROM t1 UNION SELECT f1,f2,f3 FROM t2 Assuming the structure is exact across all three tables, the schema you're using is public, the geometry field is called geom and fields are ordered the same way: CREATE TABLE public.master AS SELECT * FROM region_1 UNION ...


9

Some of your data is not in 4269 as expected. Maybe it has already been converted to 102008 or else. You can query all records that are out of bounds: select * from data.studies_ln_reg_07 where abs(st_x(geom)) > 180 or abs(st_y(geom)>90; example: select st_transform(st_setsrid('point(575 90)'::geometry,4269),102008); ERROR: transform: latitude or ...


8

Your statement looks like a random chain of SQL related commands, or a MySQL/MS SQL Server command (see @ConcreteGannet's answer), so in accordance with your questions tags, the actual solution to this issue is likely to properly introduce yourself to the PostgreSQL dialect, and the PostGIS extension. The correct set of commands would be to add the geom ...


8

If the point is inside a polygon the distance from the point to polygon is zero. Query the distance from the point to the boundary of the polygon instead. SELECT ST_Distance('SRID=25832;POINT(641099.6 5495250.5)'::geometry, ST_Boundary('SRID=25832;MULTIPOLYGON(((641101.557 5495236.647,641100.718 5495237.413,641099.327 5495238.694,641091.793 5495246.089,...


8

The convex hull algorithm is somewhat delicate, and ST_ConvexHull makes no effort to preserve and validate topology and geometry - it is possible that parts of the constructed hull collapses into lower-dimensional geometries during the process. You'd need to dump the GEOMETRYCOLLECTION, and filter for a specifc geometry type: CREATE TABLE boundary AS ...


8

UpdateGeometrySRID only ever sets the SRID identifier (and changes the typemod accoridngly), it does not reproject! This scenario is covered by the docs: If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot you can do this with DDL but there is no equivalent PostGIS management ...


8

Use a correlated [NOT] EXISTS filter: SELECT * FROM foo WHERE NOT EXISTS ( SELECT 1 FROM bar WHERE ST_DWithin(bar.geom, foo.geom, <dist>) );


8

You can use SpatiaLite tables as if they were PostGIS tables but without importing them physically by using the OGR Foreign Data Wrapper https://github.com/pramsey/pgsql-ogr-fdw. Once the virtual FDW table is created you can query the SpatiaLite database with a PostgreSQL client. Also ogrinfo and ogr2ogr can automatically see the FDW tables. A complete ...


8

To get the geometry use the ST_Intersection(): Returns a geometry representing the point-set intersection of two geometries. In other words, that portion of geometry A and geometry B that is shared between the two geometries. select t1.id, st_intersection(t1.geom, t2.geom) geom --Here! from public.table1 t1 inner join public.table2 t2 ...


7

It seems "included" here means intersects, rather than true containment; you want to run SELECT nw.* FROM "NeighbourhoodWatch" AS nw JOIN gis_streets AS gs ON ST_Intersects(nw."NeighbourhoodWatchGeometry", gs.geom) ; Choose the actual SELECT list as needed. Note that you'd do yourself a favor by avoiding anything but ...


7

Functions and procedures in the PostgreSQL environment are costly due to their overhead, compared to the directly interpreted SQL. And functions are, indeed, a performance fence (or rather, a non-optimizable container) for the planner, in particular cases, and especially when used in complex statements. While there are plenty of scenarios where a complex ...


7

Generally, in SQL you specify the (schema qualified) relation (table) identifier (i.e. public.branches_atms_0601 as part of the FROM expression, and optionally add an ALIAS (e.g. AS br_at), after which you can use that identifier (or ALIAS exclusively, when added) throughout the query. However, it is required that all relations used in the query are somehow ...


7

If you want a 1 degree grid, make your reference system by geographic (eg 4326) and then make a 1 unit grid, with your shape providing the bounds. with grid as ( select (st_squaregrid(1, st_transform(geom,4326))).* from admin0 where name = 'Canada' ) select st_astext(geom), i, j from grid;


7

You should use ST_Translate for this intent https://postgis.net/docs/ST_Translate.html UPDATE your_table SET your_geom_column_name = ST_Translate(your_geom_column_name, 4, 4); Be aware 4 are units from your geometry projection. Would be better if meters instead of degrees as 4 degrees something like hundred of kilometres...


7

To fix the error, use the function st_setSrid instead of st_srid (the former sets the projection, while the later informs about the currently set projection). Once you have done your type/projection, you can cast the output to the specific geometry type/projection. st_setsrid(st_multi((array_agg(geom order by id_finca))[1]),3857)::geometry(MultiPolygon,3857) ...


7

The issue you are facing is geometric validity: the resulting Polygon geometry of an ST_MakeEnvelope of a Point (i.e. when used with the same coordinates) is invalid, and all relationship functions then short circuit to false. ST_Extent returns a bounding box (BOX3D data type), for which these functions resolve in simple min-max comparisons without geometric ...


7

You need to use a QGIS Python Macro. QGIS Expression is not supported in the Query Builder. Go in Project properties then Macros, the code is under the screenshot : Python code to use, you need to replace the layer_id variable : from qgis.core import QgsProject def openProject(): project = QgsProject.instance() base_name = project.baseName() ...


6

The SQL should pick the first value To give an accurate answer a definition of what is "first" is needed. I mean, it could be just the first random result returned by the database or it cat be sorted by some criteria. One easy, probably inaccurate, way of getting a result, is to use an aggregate function in the additional desired columns: CREATE ...


6

I think what's going on here is that when you write: from schema1.site as point, schema2.buffer as poly PostgreSQL is doing a CROSS JOIN between the two tables. When multiple tables are listed in the FROM clause postgres uses a CROSS JOIN source that results in a table with a number of rows equal to the Cartesian product of the two tables source. to avoid ...


6

It might be faster to use the polygons as the driving table, using the point table index to filter down the large number of records. This allows PostGIS to optimize the ST_Intersects spatial predicate by preparing each polygon. This can be forced using LATERAL: SELECT pt.* FROM schema2.buffer AS poly JOIN LATERAL (SELECT * FROM schema1.site) AS pt ON ...


6

If you want to remove it from the HTML feature info, you can modify the Freemarker template generating the output, which looks as follows: https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/resources/org/geoserver/wms/featureinfo/content.ftl This guide shows how to setup your custom feature info templates: https://docs.geoserver.org/latest/...


6

Resolving spatial relationships between adjacent geometries which do not share exactly same vertices tends to be a bit unreliable because of inaccuracy of floating point computing. In this case the first polygon (on the right) does not have a vertex around the top-right corner of the second polygon. The ST_Overlaps and ST_Intersection must create an ...


6

You can use st_translate to move the point SELECT ST_MakeLine(geom, st_translate(geom,0,-500)) FROM liechtenstein.punkte_test;


6

If I understand correctly, you need to change your SQL from: "SELECT * from Bauwerkslinie where begehung_von IS NOT NULL" to "SELECT *, 99 as transactionid from Bauwerkslinie where begehung_von IS NOT NULL" where "99" is whatever value you want in the new column. This works for me with a simple geopackage example. If ...


6

All PostGIS types and functions are optional; if you do not use them, you will not notice them. You can simply switch it on and off with CREATE EXTENSION postgis; and DROP EXTENSION postgis;, so it is easy to test.


Only top voted, non community-wiki answers of a minimum length are eligible