Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

Filter by
Sorted by
Tagged with
4 votes
1 answer
36 views

How to change the port-number of a PostGIS layer's data source outside QGIS?

I have several QGIS project files (qgz) I didn't touch in quite some time that contain several layers heavily depending on a PostGIS connection and meanwhile its port number was changed (everything ...
maxwhere's user avatar
  • 1,158
2 votes
0 answers
44 views

PostGIS - tiling vector layer

I have a height isoline vector layer covering a large area, but most of the time, I am only working on a small part of it, so all kind of handling of the data goes slowly. Are there any ways to make a ...
MortenSickel's user avatar
  • 3,686
3 votes
1 answer
78 views

PostGIS query to get the closest data available

I've got three tables in a PostgreSQL/PostGIS database: referencia.comarcas_agrarias_centroides (holds geom centroids of agricultural regions) referencia.estaciones_r_pen (holds geom locations of ...
jpinilla's user avatar
  • 3,230
1 vote
0 answers
74 views

Achieving ArcGIS Dissolve result with PostGIS

I want to merge two or more adjacent lines (within a XY tolerance) to one line. The result from Dissolve in ArcGIS is what I aim for. How can I do that in PostGIS? See figure below.
Paul's user avatar
  • 477
2 votes
0 answers
56 views

How to make ST_SetZ respect NODATA?

ST_SetZ looked like a very useful new function to replace this sort of thing, especially when point is a complex expression: ST_SetSRID(ST_PointZ(ST_X(point), ST_Y(point), ST_Value(rast, point)), ...
OrangeDog's user avatar
  • 251
2 votes
1 answer
68 views

KNN (<->) order by -- possible to use spatial index with a secondary order by?

From reading here as well as the docs, I know how to use the <-> operator in my order by in order to do KNN search, and this is fast and uses the gist spatial index as expected. However, I have ...
Loose_Screw_748's user avatar
3 votes
0 answers
58 views

Are there ways to speed up building large topology datasets in PostgreSQL/PostGIS?

I used this stored procedure to create a topology for all Census block groups in the contiguous US. It took almost 136 hours to run (on a fast, dedicated workstation - Ubuntu 23.04, PostgreSQL 16, 128 ...
Stonetip's user avatar
  • 187
1 vote
1 answer
36 views

PostGIS: DEALLOCATE addfeatures returned 7 [ERROR: prepared statement "addfeatures" does not exist

I have a connection to a PostGIS database in QGIS and am able to connect and view features successfully, however when trying to add new features I continue to get the following error message: WARNING ...
C-Lambert's user avatar
2 votes
1 answer
71 views

PostgreSQL ST_Within query not working

I'm using Psycopg2 and PostgreSQL to try and isolate points from within polygons for ArcGIS. I generate a spatial condition from coordinates and can then pull all the points within that spatial ...
matt cooper's user avatar
2 votes
1 answer
58 views

Unexpected results using ST_ClosestPoint: order of arguments make no difference on non-intersecting objects

(Note, this is a revision of the original question. I removed distracting information, without changing the original intent of the question) In this query: select ST_AsText(ST_ClosestPoint(line, ...
Terry Weymouth's user avatar
3 votes
2 answers
143 views

Why following ST_Intersects SQL returns false

I run following code in PostGIS. Both SRID is default, which is 4326. select ST_Intersects('POINT(104.042455 30.657381)'::GEOGRAPHY(POINT), ST_MakeEnvelope(80, 30, 140, 40, 4326)::GEOGRAPHY(POLYGON)) ...
timewalker's user avatar
3 votes
0 answers
83 views

Slow KNN within fixed radius search (PostGIS) - ST_DWITHIN vs KNN

Ask: I want to return all customers matching certain characteristics within a 50 mile radius of the incoming zip code. This query takes just over a second to complete, and that's because of this line: ...
Loose_Screw_748's user avatar
0 votes
1 answer
129 views

QGIS and remote PostgreSQL/PostGIS (in cloud) slow performance

I am using QGIS 3.28.11 version and in backend I am using PostgreSQL 15.0 with PostGIS 3.3.3. I have total 36 layers which contained in 3 groups: A, B, C. Now X user has read only access on all layers ...
Rajesh Kumar's user avatar
1 vote
0 answers
52 views

pgrwithpointscost returning empty table

I have a road network and points along the road network that I would like to calculate the distances of the points from beginning to end. Previously @geozelot helped me with a similar problem, but ...
MJM's user avatar
  • 975
1 vote
0 answers
36 views

QField app with live PostGIS connection photos won't synchronize

I am trialing QField and qfield cloud with organization plan. I have managed to setup live connection between QField and my PostgreSQL/PostGIS database. My problem is that even if I have a photo type ...
Tamas Kosa's user avatar
  • 1,507
1 vote
1 answer
54 views

PostGIS - step in state load (loader_load_staged_data) is producing more columns in select than in insert

The loader_load_staged_data function, called from the script generated for states by Loader_Generate_Script, is failing when attempting to insert faces data. The uace20 column is present in the select ...
user717847's user avatar
0 votes
1 answer
50 views

I would like to import PostGIS' Raster Layer into QGIS using SQL. Is there a way?

I imported PostGIS Raster Layer using PyQGIS. Can I apply SQL to it? uri = QgsDataSourceUri() uri.setConnection("localhost", "5432", "dbname", "user", "...
Jihyun Kim's user avatar
3 votes
1 answer
229 views

QGIS - Is it POSTGres or POSTGIS?

I am confused, sometimes I see people in videos using PostgreSQL and other times I see PostGIS. My QGIS install has PostgreSQL. They both have the little elephant. Can anyone explain why I see two ...
user231744's user avatar
0 votes
1 answer
69 views

Upgrading PostGIS 3.2.0 to 3.4.0 doesn't update

I run the following commands, but the PostGIS version continues to be 3.2.0. SELECT postgis_extensions_upgrade(); SELECT PostGIS_Version();
MJM's user avatar
  • 975
4 votes
3 answers
189 views

PostGIS distance query using a dynamic radius

(Background: I'm more of a MySQL guy, I've only been using PostGIS for a few months to solve this particular problem set.) I have a table that contains a point and a radius: id | location ...
Daniel Orner's user avatar
2 votes
1 answer
71 views

Union of multiple polygons by value that ST_Touch

I have a table of polygons with a column 'class'. In that column are 25 different categories; however, I am only concerned with three polygon classes: marsh, water, and grass. I would like to ...
MJM's user avatar
  • 975
2 votes
1 answer
53 views

Combining two shapefiles containing roads of the same area

I have two layers both containing roads of the same area. One si from OSM, and the other is from another source. Both networks are quite dense and for the most part, they contain the same roads, ...
serta8199's user avatar
1 vote
0 answers
52 views

ST_ASMVTGEOM returning null for small complex polygons at low zoom

I have polygons that have around 150 ft2 area (according to ST_Area) and I'm having problems getting an MVT geometry from them. They are pretty complex as you can see, many points as they are actually ...
Francis Ducharme's user avatar
3 votes
2 answers
61 views

Why is my list sometimes including false references in my PostgreSQL view?

I have a problem on this view : CREATE OR REPLACE VIEW public.v_izae_unites_foncieres_13 AS SELECT row_number() OVER () AS ogc_fid, foo.code_insee, foo.nompro, foo.adr_prop, foo....
Jules Baraton's user avatar
2 votes
0 answers
47 views

Why do I get this result when merging small polygons

I would like to merge small, thin polygons to continuous polygons a when they intersect. This is a section of the original polygons: And this is my result: I'm using this SQL query: CREATE TABLE ...
Paul's user avatar
  • 477
0 votes
2 answers
71 views

Missing line segment when serving a vector tile from PostGIS

I'm hosting a table of four points in PostGIS and querying it to create a vector tile layer of a line connecting the points. The second and fourth points in the table are the same. The resulting ...
mhkeller's user avatar
  • 181
1 vote
0 answers
50 views

How to get a bounding box in one SRID of a bounding box in another SRID?

I have a bounding box in SRID 3857, web mercator, gotten from a UI where a user is panning and zooming. I need to query points in postGIS within this box. However, my points are all stored in SRID ...
Him's user avatar
  • 130
1 vote
1 answer
51 views

Using QGIS to create XYZ .png tiles on EC2

I have set up a PostgreSQL/PostGIS DB on an EC2 that contains my .osm data. I populated it with osm2pgsql. Now, I can create tiles on my local machine using the QGIS "Generate XYZ Tiles (...
rm_'s user avatar
  • 11
0 votes
0 answers
23 views

Merge multiple tracklogs or route points to one polyline

I'm having a similar case like the one posted Tracklogs to single line OR several lines to one line?. However what differs in my question here is that I want to display the merged or common shared ...
Sandeep J Patel's user avatar
0 votes
0 answers
25 views

The jsonb field does not work correctly in QGIS

I created a trigger that should automatically populate the field, but for some reason in QGIS 3.16, the value is not displayed, only the key is populated. The key shows 09132000-3.1.3 14210000-6.3.3 ...
Miwasik's user avatar
  • 89
4 votes
2 answers
132 views

Deleting points in in certain area using PostGIS

Have a set of points cali_dairies and wanted to delete some of the locations in southern California. How do I do it?
MC Hammerabi's user avatar
1 vote
0 answers
25 views

Why does ST_DelaunayTriangles return an empty GeometryCollection?

I'm currently struggling to calculate the area of a slightly non-planar surface in PostGIS. The area is a wall surface from a LOD2 data source. Here's an example: SELECT ST_AsText(ST_DelaunayTriangles(...
Chris's user avatar
  • 11
3 votes
2 answers
149 views

ST_ReducePrecision function does not work properly

I am trying to use ST_ReducePrecision to reduce the number of characters, because of the character limit in power bi cells. ST_AsText(ST_ReducePrecision(ST_Transform(geom, 4326), 0.000001)) The result ...
Matheus Clarkson's user avatar
4 votes
2 answers
247 views

Cryptic PostGIS/GEOS topology error

I'm attempting to determine 1) what "ring edge missing" means in the following PostGIS/GEOS error message, and 2) why ST_Intersection throws it for certain gridSize parameters but not for ...
Geolographist's user avatar
2 votes
0 answers
49 views

How to cluster and paginate data locations on a map

I have many data points on my map and want to paginate the results into a fixed number of results per page. What I need to figure out now is how to rank or basically decide which location goes to ...
mitzu's user avatar
  • 21
1 vote
1 answer
36 views

ST_ValueCount for multiple bands

Given a multi band raster, I would like to summarise the distinct combinations of values found, in a manner similar to ST_ValueCount, but across all the bands rather than a single band at a time. ...
Andy Harfoot's user avatar
  • 3,367
0 votes
0 answers
43 views

QGIS 3.22.10: Automatic increment ID with Year from Date Field in PostGIS

In QGIS 3.22.10, I'm working with a PostGIS database layer containing two columns: a Art_ID column (not the primary key) and a date column in the format (yyyy-mm-dd). I need to generate an ID for each ...
cc_schr_gis's user avatar
0 votes
0 answers
66 views

Calculating from GPS time duration of a linestringM within a polygon

I am analyzing trajectories from GPS points, including timestamp (Linestring M). I need to clip trajectories that cross polygons, and calculate time spent within those polygons. However, I have 2 ...
Remi79's user avatar
  • 153
1 vote
0 answers
57 views

How to create a road graph using PostGIS

There are many cars with GPS transmitters that drive along quarry roads. These are temporary roads, they change frequently and not on the maps. GPS data is not very accurate, outliers need to be ...
zhb.hodas's user avatar
0 votes
0 answers
32 views

Editing WFS-T polygon layer gives me random geometry shape instead of current displayed geometry

I have this setup: 1-Postgis polygon layer 2-Published layer in Geoserver (granted access to everyone) 3-Configured workspace for all possible services including WFS-T 4-Disabled cached layer ...
Shefqet LULJA's user avatar
0 votes
0 answers
61 views

Filter long shaped polygons

I have a PostGIS database with a layer of 60k polygons, some of them erroneous. These erroneous polygons are extremely narrow, long shaped. Maybe I can best describe them as 'Needle' shaped polygons. ...
GIStrees's user avatar
  • 119
2 votes
2 answers
69 views

Check if polygon falls inside other polygon with 2 layers

I have two polygons layers with SRID 4326, indexed in a PostGIS database. Layer 1 is a country layer. I want to check whether the polygons (> 60k instances) from layer 2, fall inside the boundaries ...
GIStrees's user avatar
  • 119
1 vote
1 answer
28 views

PostGIS: postgis_raster out-db storage errors

PostGIS raster out-db storage doesn't work on my Windows 10 PostGIS installation. No issue with in-db rasters. SELECT postgis_full_version(); POSTGIS="3.3.1 3.3.1" [EXTENSION] PGSQL="...
bonzinor's user avatar
  • 177
2 votes
1 answer
49 views

How to intersect with ST_Buffer in PostGIS

I have a table called "gebaeude_salzburg" with all types of buildings in the city. In the following I want to use a Buffer around the central train station that has the osm_id="...
Alm94's user avatar
  • 81
-1 votes
1 answer
43 views

FME Postgres Serial - Red Attribute

I have a small table of wells with just a name, lat, lon, and geometry in an Esri Shapefile. I am putting them into a PostGIS database and it works well, so I added an arbitrary well_id as a serial ...
Andrew T's user avatar
  • 243
1 vote
1 answer
62 views

Use ST_Distance() to filter a maximum of 1000m

I've tried to select only the schools/kindergarten that are maximum 1000m distant from the train station. Here is my code SELECT gebaeude_salzburg.name AS "Name der Einrichtung", ...
Alm94's user avatar
  • 81
3 votes
1 answer
70 views

Find all geometries in one table that do not exist in another

Using PostgreSQL and PostGIS, I have two tables of polygons, and I need to know which of the records in table p1 do not exist in table p2. I do not have additional information available (e.g. ids) - ...
susie derkins's user avatar
1 vote
2 answers
62 views

ST_Distance from one location to several locations on the same table

I have a table that contains different city buildings called "gebaeude_salzburg". I want to calculate the distance of all the buildings of the type='school' and 'kindergarten' to the central ...
Alm94's user avatar
  • 81
3 votes
0 answers
36 views

Error with St_geomfromtext in PostGIS [closed]

When I try to insert a point into a point layer, getting the coordinates with the following line: select avg(st_x(the_geom)), avg(st_y(the_geom)) from centroide; I get an error when I run the ...
user's user avatar
  • 153
3 votes
0 answers
89 views

How to use ST_LineMerge or ST_Union to merge lines in QGIS virtual layers

If have an input layer with a couple of lines, called Line with an attribute. I would like to have a virtual layer with alle connecting lines dissolved, regardless of the attribute. input: expected ...
Beni's user avatar
  • 338