New answers tagged postgis
2
What you really want is to move the LIMIT clause into the pre-fetching, and order the set only for the actual aggregations!
With
a GIST on way
a plain BTREE on highway (no array => no GIN; an index has likely no effect whatsoever) and
VACUUM ANALYZE'd prior to execution
running
SELECT jsonb_build_object(
'type',
'...
0
As per ST_Volume documentation, you should make your objects as POLYHEDRALSURFACE, then convert them to solids with ST_MakeSolid and then use ST_Volume on that.
By my knowledge there is no way how to model kompletely vertical planes with simple geometry objects with Z. Overlaping coordinates (same X,Y but different Z) are considered as errors. This also ...
4
I see that you put a +towgs84 parameter in your destination crs (WGS84), but it should be included in the source crs or in the transformation (as dx, dy, dz parameters), not the destination crs.
If I transform from:
+proj=lcc +lat_1=44.33333333333334 +lat_2=46 +lat_0=43.66666666666666 +lon_0=-120.5 +x_0=609601.2192024384 +y_0=0 +ellps=clrk66 +towgs84=-8,...
1
Not sure what you're trying to do here with your editableLayers and deleted_layers, but if the point is just to move existing layers to new groups, it is fairly simple:
root = QgsProject.instance().layerTreeRoot()
root.addGroup('auxiliar')
root.addGroup('ctgeo')
root.addGroup('referencia')
for layerNode in root.findLayers():
schema = QgsDataSourceUri(...
2
Are you shure that this point lies on that border? Intersecting points with lines is like spliting hair with an axe. It is possible, but it is very hart... You have to deal with 64 bit floating point precision coordinates and if there is marginal difference between them, there is no intersection. You should try to search polygons within small distance around ...
2
Assuming that you have a guaranteed topological equality between island and water polygon rings, using a simpler EXISTS filter should increase performance:
SELECT a.id
FROM islands AS a
WHERE a.type <> 'water'
AND EXISTS (
SELECT 1
FROM islands AS b,
LATERAL ST_DumpRings(b.geom) AS dmp -- rings to polygons
WHERE ...
2
Here's another way to solve the question asked.
Run the script:
WITH
tbla AS (SELECT * FROM islands WHERE type = 'land'),
tblb AS (SELECT ST_InteriorRingN(geom,1) geom FROM islands WHERE type = 'water')
SELECT ID, type, (a.geom) geom FROM tbla a INNER JOIN tblb b ON ST_Intersects(a....
1
The query will work only for islands placed in the first interior ring of a water body, and for topological correct geometries. Your case fills both requeriments.
The only change is that you are referring as table a as if they were the land bodies, but you are filtering a.type = 'water', and the opposite for the table b.
SELECT a.id
FROM islands a
...
2
The new (or modified) row is available in the trigger using the NEW record. You can directly use its geometry or any other attributes to query the 2nd table. The output of the query is then saved in the NEW record, and when done the entire record is commited.
CREATE OR REPLACE FUNCTION public.tg_f() RETURNS trigger AS
$BODY$
BEGIN
SELECT ...
0
Calculating, No. Looking up, Yes.
Pre-calculated data is available for the various Geoid models (EGM84, EGM96, EGM2008) in raster format. Here's an easy to use source:
https://sourceforge.net/projects/geographiclib/files/geoids-distrib/
or
https://geographiclib.sourceforge.io/html/geoid.html#geoidinst
The raster files can be imported into your db using ...
1
QGIS support for blobs is currently limited to disk-based layers like gpkg and memory layers, not databases. (See Load PostGIS Layer with BLOB and use the form widget for binary fields (QGIS 3.6) not working )
If you can live with this limitation, or if blob support is later expanded to additional providers, see my question and answer here: Display photo ...
6
You will need to tell PostGIS how to coerce the TEXT values into GEOMETRYs; the implicit type cast is able to cast WKT/WKB between TEXT and GEOMETRY (while you provide either GeoJSON or...well, two numbers as TEXT).
Run
ALTER TABLE <table>
ALTER COLUMN localisation TYPE GEOMETRY(MULTIPOLYGON, <SRID>)
USING ST_GeomFromGeoJSON(localisation)
...
1
When you simply read a geometry value from Microsoft SQL Server, you get a blob in the CLR Type Serialization Format, which is different from WKB.
If you have an SQL Server database, you can cast that value back into the geometry type (although it would be a better idea to export the value as WKB or WKT to begin with).
If you do not have SQL Server, you ...
3
QGIS queries and fetches indexed sources based on the canvas position/extent, plus tolerance;
the Features count is the total count of entries in the table
the Filtered count is the amount of features that has actually been loaded from
DB
Try it; the filter count changes when you pan/zoom.
0
You can simply join your vertices to edges and then use vertex close/open information to set edge cost acordingly. I supose your graph is directed, so then you want only left join on target vertex because when target vertex of odge is closed, then that edge should be removed from graph by setting its cost value to -1. Something like this should work:
SELECT ...
0
pgRouting's network topology does not handle nodes but only edges, so you have to apply costs of the nodes to the edges.
2
If instead single isolated segments, you need to identify sub-graphs that are isolated from the rest of the graph, then using pg_routing pgr_connectedComponents() function will separate all nodes into connected sub-groups (components).
You can then see which component represents the bigger sub-group of the graph (hopefully component=1), and see what is ...
3
QGIS works in 2 dimensions for most its implicit functionality; there are tools to add Z values to the source layer, either from attribute or secondary data, but the edit mode will coerce the geometry type into 2D.
I suggest to work with a View here, having 2D geometries and an INSTEAD OF trigger in place to reroute edits to the 3D base table:
create the ...
2
You can directly verify this with QtSql, see the following Python3 code :
from PyQt5.QtSql import QSqlDatabase
host = "LPOSTDES-CL2"
port = 5432
database = "ugpi"
db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(host)
db.setPort(port)
db.setDatabaseName(database)
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if ...
3
Since the values of Z are at 0, you can create a new table on PostGIS (or update) without a z value by using ST_Force2D function.
For example :
DROP TABLE IF EXISTS new_table_without_z_value ; -- replace with the name of your future table
CREATE TABLE new_table_without_z_value AS -- replace with the name of your future table
SELECT ST_Force2D(geom) as ...
1
The @JGH comment showed the way to solve the problem. It is possible to compare original geometry g with the simplified one by ST_Equals:
SELECT ST_equals(g, ST_SimplifyVW(g,0.000000000001) ) eq FROM t; -- false...
SELECT ST_equals(g, ST_SimplifyVW(g,0.0000000000001) ) eq FROM t; -- true!
Checking all possibilities:
WITH calc AS (
SELECT tol, g,
...
3
It is in fact very hard to determine if a point is exactly - or not - on a line, because the coordinates have 15 decimals, which are just noise unless you specifically mention them.
What you could do is to look for points that are very very close to the line
SELECT *
FROM A
join B on ST_DWithin(A.coordinates::geometry, B.coordinates::geometry,0.0001);
...
1
You can add and manipulate fields in your query
SELECT
a.com || '_' || b.com as com
ST_INTERSECTION(a.geom, b.geom) as geom
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);
0
Based on an example here, you could do something like this:
SELECT
a.station_id,
ST_Distance(a.geom, 'POINT(your_lon your_lat)'::geography) dist
FROM rest a
WHERE ST_DWithin(a.geom, 'POINT(your_lon your_lat)'::geography, your_dist_in_meters)
ORDER BY a.geom <-> 'POINT(your_lon your_lat)'::geography;
1
Tested approach mentioned.Results:
Script posted here should do the job.
0
You should try to use raster2pgsql instead of generating huge text files. On the tiff version (it should work with the other version too), I used it like that:
CREATE SCHEMA mnt;
raster2pgsql -d -C -I -M -t 200x200 -l 2,4,8 /data/*.tif public.mnt | PGPASSWORD=pass psql -h localhost -U your_user-w -d your_base
The -l 2,4,8 is to create different aggregates ...
0
The user running the query does not have public in its path.
The 1st query does not throw a not found error because the path is explicitly written (SELECT public.AddGeometryColumn ...)
You can check if the schema where PostGIS is installed is in the user search path by issuing
show search_path;
If not, you can permanently add the path by altering the ...
0
It's easy to get it,
if I have understood the translation correctly by looking at the picture you presented.
Input data is shown in the figure
Run the script:
WITH tbla AS (SELECT ST_Union(geom) geom FROM grid),
tblb AS (SELECT ST_ExteriorRing(ST_Buffer((ST_Buffer((geom),-0.0001)), 0.0001)) geom FROM tbla)
SELECT (a.geom) geom FROM grid a JOIN tblb b ON ...
0
Did you try using a simpler way to install postgres and PostGIS.
Try out these code :
> sudo apt update
> sudo apt install postgresql postgresql-contrib
3
One way to produce a polygon which uses all points in a set is to create a star polygon around the centroid of the points. Whether this produces an acceptable result depends on the positions of the points. But in this case it seems like this will do exactly what is required.
The algorithm is simple:
Compute the centroid of the set of points
For each ...
2
The query does not work because it looks for single polygons having both id 1 and 2, which is not possible.
Instead, you could restrict the polygons to either id 1 or 2, then to intersects with the line and at last keep the lines having 2 intersections:
SELECT lines.id, lines.geom
FROM lines
JOIN polygons ON st_intersects(lines.geom,polygons.geom)
WHERE ...
0
To expand on Martin Jensen's answer, I set that environment variable in my shell which was invoking ogr2ogr, as well as setting PGCLIENTENCODING. Like so:
export PGCLIENTENCODING=UTF-8
export NLS_LANG=AMERICAN_AMERICA.UTF8
ogr2ogr -f "Postgresql" <PG conn string> <oracle conn string> ...
and now my encoding is working as expected. Without ...
1
This is the final query I made, using a subquery. The source geometry (a field named geometry) is in SRID 28992, to be transformed into SRID 4326. The output is GeoJSON.
SELECT
( SELECT ST_AsGeoJSON(ST_Transform(ST_SetSRID(ST_MakePolygon(ST_ExteriorRing((ST_Dump(geometry)).geom)), 28992), 4326)) AS simplified
WHERE provincienaam = P.provincienaam
...
2
Finally figured this out. psycopg2 cursor fetches memory view into geom_wkb which needs to be converted to bytes, then you need to have some existing QgsGeometry which you update by received bytes and finally set feature's geometry to updated geometry:
db_cursor.execute('with geom_update as (select ... from {0} where gid={1})'
...
3
this should do the trick -- just replace your tables and columns where needed
SELECT a.id,
a.geom,
avg(c.temp_val) temp_val
FROM tablea a
CROSS JOIN lateral
(SELECT temp_val
FROM tableb b
ORDER BY b.geom <-> a.geom
LIMIT 4)c
GROUP BY a.id,a.geom
the idea is to take table a (outer table) 1 record at a time and find the 4 ...
4
Envelopes are often very inaccurate, depending on the coordinate reference system (CRS), or converting an envelope between two CRS. An envelope is 2 points, so projecting or transforming them to a different CRS means you can miss distortions occurring at the other two corners, on the edges, etc. An envelope in one CRS, if it was densified first, many times, ...
2
Unfortunately, if there are clustering functions in Postgis, there is still no density functions. But you can create your own, and luckily somebody already did: https://trac.osgeo.org/postgis/ticket/2894#no1
0
I suggest you use the PostGIS tool to load the shapefile. Please check the following link:
https://postgis.net/workshops/postgis-intro/loading_data.html
0
You can use Create Lines from Points in Builder selecting Sequencial or use a more classical approach using ST_MakeLine PostGIS function ordering your points based on a custom field (time_field in my case) with a query similar to this one:
ST_MakeLine(the_geom ORDER BY time_field ASC)
3
When you store geometry in 'Multi' postgis types, geoserver also expects this 'Multi' in transaction query. Leaflet-WFST plugin can create Multi-geometry requests on simple geometries by option 'forceMulti', this option available since v2.0.1-beta.18
var wfstPoly = new L.WFST({
...
forceMulti: true
...
}).addTo(map);
1
I don't really get why you can't use Geography data directly, and let Postgis do the work. This kind of computation usually work in a few ms, the computation itself is generally way less than or the other costs (like reading data). If you have performance issues, maybe the problem is not the projection but the request or the indexes.
If you really are in a ...
2
So, let's summarize for one piece of geodata.
1) Input data are axial lines of roads of one Amsterdam tablet;
2) The length of red lines is not uniform, it is present either on one side or on two sides of quarters and in some places has the bent form;
3) Distances between the red lines and the green lines are different;
4) When trimming lines at ...
0
Found a solution. Probably not the best and easiest, but it works. It also has nice side effect regarding performance!
Gdal_contour basically draws a line between all pixels with the same value. Of course it doesn't see outside the tile, how the line would continue. That's why the line isn't equal between two adjacent tiles at their border.
The solution ...
1
I can think of two solutions:
Dump your raster as polygons using this function ST_PixelAsPolygons(). Then, do your calculations for the centroid of each polygon (first calculate the centroids of the polygons). Now, you should have a table of points that each of them has a value that you calculated. Finally, build an array of your points (a geomval object) ...
0
We solved the problem: During a database-upgrade from pg 9.1 to pg 12 some of the "old" postgis functions were restored to the new database. After creating the new postgis-extension we had some functions twice or more times, which led to the problems.
To solve the issue we created a new database with a fresh postgis-extension and transferred just the data ...
1
From @ThingumaBob comment:
alias your PK column AS fid: ogr2ogr wants that identifier for GML, and it seems the driver will add that field in any case.
Aliasing the collumn I wanted as fid worked and no new attribute field was created when exporting to gml.
1
It should be possible to do with gdal_rasterize https://gdal.org/programs/gdal_rasterize.html#gdal-rasterize.
You could for example create a set of buffered geometries for your source data. Burn little values with the largest geometries first, and then increase the pixel values by burning the smaller geometries into the raster by using the -add option
-...
2
This is a classic road network conflation problem. It has the following challenges:
determining a set of matched road segments to a path line in spite of them being very different lengths
clipping the matched road segments to the path line (this is best done after merging the matched road segments
(The "path lines" are the red lines in the diagram)
For ...
1
The "lines and points" in the geometry are actually very narrow/small holes. So it seems like you are looking to remove all the holes from a MultiPolygon. An approach for this is:
"Explode" the MultiPolygon into separate Polygons using ST_Dump
Remove the holes from each element Polygon using ST_MakePolygon(ST_ExteriorRing(poly))
Combine the hole-free ...
1
I would recommand to process the spatial relationships within the vector format, then rasterize the result. It seems that some buffers around your vectors combined with priority rules would work quite well. All the operations that I know between raster and polygon in fact imply a format conversion at one stage. So either you create the buffers first, then ...
Top 50 recent answers are included
