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.
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
)) AS geom
FROM table1 AS a
CROSS JOIN ...
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
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 ...
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 ...
Use UNION or UNION ALL, depending on whether you want duplicates. Example:
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
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;
select st_transform(st_setsrid('point(575 90)'::geometry,4269),102008);
ERROR: transform: latitude or ...
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 ...
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,...
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
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 ...
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 ...
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.
st_intersection(t1.geom, t2.geom) geom --Here!
It seems "included" here means intersects, rather than true containment; you want to run
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 ...
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 ...
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 ...
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;
You should use ST_Translate for this intent https://postgis.net/docs/ST_Translate.html
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...
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))),3857)::geometry(MultiPolygon,3857) ...
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 ...
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
project = QgsProject.instance()
base_name = project.baseName()
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:
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 ...
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
If you want to remove it from the HTML feature info, you can modify the Freemarker template generating the output, which looks as follows:
This guide shows how to setup your custom feature info templates:
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 ...
If I understand correctly, you need to change your SQL from:
"SELECT * from Bauwerkslinie where begehung_von IS NOT NULL"
"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.
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.