Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
4,024
questions
2
votes
2
answers
30
views
PostGIS subquery error when trying to randomly distribute points in polygons
The following is some code from @geozelot that I'm repurposing to randomly distribute duplicate points inside the polygon they are contained within, however, I get the following subquery error:
ERROR:...
- 605
2
votes
2
answers
206
views
Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?
As seen here, it is possible to use PyQGIS to load layers from PostgreSQL using an instance of QgsDataSourceUri(). You then set the connection and load a layer.
Loading PostGIS layer from SQL query ...
- 69
2
votes
0
answers
29
views
We have lost permissions on the topology schema
We have a PostgreSQL database with PostGIS, with the following versions:
PostgreSQL 10.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
...
0
votes
0
answers
17
views
PostGIS layer in ArcMap 10.7 - Can't open table - no OID recognized
I have this line feature class that FME extracts from the ESRI geodatabase in order to write (update periodically) in PostgreSQL (using PgAdmin 4). The original feature class has an objectId and it ...
- 9
2
votes
2
answers
38
views
I need to expect multi value with if statement
I have field called name which has values like Ahmed and Marten.
I need to check if this is the title without considering and.
The answer is no for if(name is title(name) ,'yes','no') because of and.
...
4
votes
2
answers
93
views
Unmatched records missing from spatial left join
I have a polygons table and a points table.
I want to get for each polygon the number of points located in it and have zero for polygons with no points.
However with the following query I miss the ...
- 753
1
vote
0
answers
31
views
Zonal statistics for very large point layer with buffer
I have a point layer and I need to calculate, for each point, the area of overlapping raster layer within 500m distance. This could be done fairly quick by generating a 500m-buffered vector layer from ...
- 11
2
votes
0
answers
30
views
PostGIS 3.3, ST_DWithin Query - "lwgeom_distance_spheroid returned negative!" by changing third arg? Postgres 11, Ubuntu 20.04
The unexpected error:
lwgeom_distance_spheroid returned negative!
By making a tiny change in the third argument (distance) to ST_DWithin, we get an error in one version but not another. In other words,...
- 21
2
votes
1
answer
46
views
Using a intersects spatial query as default value in PostGIS table
I have two PostgreSQL tables :
table_a (polygon) with field_1
table_b (polygon) with field_2
For field 2, I would like to get the corresponding value in field_2 of the polygons of table 2 inside or ...
- 487
6
votes
1
answer
349
views
Documentation for <->
I found this question and am wondering, is it for PostgreSQL? Where’s the documentation for <->? Trying to find the fastest way to get the distance between a lat and long earth coordinates in ...
- 161
1
vote
0
answers
17
views
How to get all streets from pbf for one city using SQL?
I have a file russia-latest.osm.pbf, which is imported into postgreSQL in default form and with hstore support.
For example, I get a list of all cities, where the population is more than 200 000 ...
- 11
1
vote
1
answer
43
views
Convert SQLite3 Geometry BLOB to WKB for PostgreSQL
I have used iterdump() to create an output file from a SQLite database that includes SpatiaLite data. I need to convert the hex data to a geometry in PostgreSQL.
I have found here a summary of how the ...
- 449
5
votes
1
answer
80
views
Calculate distance between two consecutive points within the same group in PostgreSQL
I have a table with Point geometries representing GPS data of participants. I am calculating the distance between two consecutive points:
SELECT id, participant, ST_Distance(geom, lag(geom, 1) OVER (...
- 53
1
vote
0
answers
30
views
How to reorganize PostgreSQL layer fields in QGIS 3.28
I have a PostgreSQL layer in QGIS, and I have organized the attribute table fields as desired. However, when I copy the features from the attribute table and paste them into an Excel sheet, the ...
3
votes
2
answers
136
views
GeoJSON from SQL View
I am trying to select database table features within a certain distance from a leafleat generated geosjon using an sql view in geoserver. I first used a GET function and a user here suggested POST. I ...
- 221
0
votes
0
answers
19
views
PostgreSQL identity/generated columns do not work with Append to Esri Enterprise Geodatabase
I want to set a column as an integer primary key separate from what Esri maintains. This is so it can exist in non-spatial tables and the features retain the key as they exist across other tables.
The ...
0
votes
1
answer
22
views
Ogr2Ogr appending duplicate records (2X Times) to PostgreSQL Table
I'm synchronizing data from AmigoCloud's table with a PostgreSQL table.
I'm using GDAL's ogr2ogr module in osgeo environment. The command I'm running is as follows:
ogr2ogr -append -f PostgreSQL PG:&...
- 39
2
votes
0
answers
20
views
Is there any PostGIS st_makevalid equivalent for use in Esri's sde.st_geometry?
I am inserting into an Enterprise Geodatabase (Postgres running sde.st_geometry).
I am encountering errors on inserts when a multipolygon has parts with a zero-area.
I know of Desktop's Repair ...
1
vote
1
answer
34
views
Error inserting data into PostGIS/PostgreSQL database through postgres_fdw Foreign Data Wrapper
I'm trying to insert data (a line geometry) into a foreign table which was mapped from a view in a PostGIS/PostgreSQL database. That view in turn, check if the the line is inside a polygon before ...
- 1,934
0
votes
0
answers
27
views
Transform raster of unknown SRID using proj4 in PostGIS
In PostGIS is there any way to transform raster of unknown SRID using proj4 to EPSG:3857 without dumping polygons?
1
vote
1
answer
42
views
Create half ellipsoid on Postgis
I have a positions of half ellipsoid, these numbers represent coordinates of ellipsoid:
But when I try to save this coordinates, it throws an error like:
Geometry has Z dimension but column does not
...
- 11
1
vote
1
answer
55
views
How do I solve "django.db.utils.OperationalError: near "CASCADE": syntax error" in GeoNode install?
I have followed the Advanced Installation instructions for GeoNode Project, but at the Run GeoNode Project for the first time in DEBUG Mode step, it says to execute:
# Prepare the GeoNode Spatialite ...
0
votes
0
answers
36
views
ArcGIS Pro (3.0.4) "Share as web layer" fails with error 00231, despite the database already being registered as an ArcGIS Server data store
I have a PostgreSQL database instance running in the Azure Database for PostgreSQL service. I am able to successfully connect, create enterprise geodatabases within, and load feature classes. I am ...
- 91
1
vote
0
answers
22
views
QGIS - Connect to DB using Task Manager (problem)
As I have a lot of layers, I am writing a plugin that will import layers through TaskManager. Script imports layers successfully into the project with some minor problems. When layers are loaded I can ...
- 11
2
votes
0
answers
36
views
How to check if blocks touch by walls and continuous?
I have a number of blocks that are lying next to each other in all directions.
So, the user can select individual and multiple blocks.
There is a "must" requirement to select multiple ...
- 41
3
votes
1
answer
86
views
Inserting polygon as GeoJSON in PostGIS with only boundry coordinates
I am trying to insert GeoJSON polygon features into a PostGIS DB. The input coordinate points are only the left-top corner and the bottom right corner. I have tried to use the ST_MakeEnvelope, but it ...
- 135
3
votes
0
answers
46
views
Make a QGIS project stored on postgresql read only for specific users
I would like to restrain the privileges of certain users of my QGIS project stored on my PostgreSQL database to certain users.
I would like to make it read-only for everyone but myself.
Other users ...
- 487
2
votes
2
answers
59
views
Override WFS for transactions only
WFS service in Mapserver directly changes the PostgreSQL table whenever performing a transaction operation such as insert, update or delete.
I want a solution which gives me control over what happens ...
- 39
0
votes
0
answers
25
views
Syncing QGIS with QField using PostGIS data
What I want to do is to have an online connection with PostgreSQL database on a QField. So if you change something with QField it will be written in a DB without using QField Sync.
I found a video ...
- 1
1
vote
0
answers
31
views
ST_Contains returns false even when the point is inside polygon [closed]
I am trying to map a set of points to polygons if the polygons contain the points. However, there are some points that are not being mapped. One such point and the polygon are below in the code ...
- 11
0
votes
0
answers
21
views
QField SSL authentication and live project updates
I'm currently having some issues with QField saving to a PostgreSQL database.
I believe that the issue lies with SSL being forced on the database end as i am able to have offline files work on QField ...
- 101
0
votes
1
answer
41
views
ST_Boundary for geography polygon
I'm trying to calculate the intersection point between a segment and the exterior ring of a polygon. But I'm not sure how to do it correctly when dealing with geography types.
The segment is a line ...
0
votes
0
answers
27
views
Stop processes if pgsql2shp/shp2pgsql fails
I have a process where data is extracted from PostgreSQL (PostGIS) to shapefiles. The data is used as input creating new data (shapefiles) through a program outside PostgreSQL.
The new data is then ...
- 1
0
votes
1
answer
69
views
Locate point along line in multiple self-intersection area
I've a not simple linestring geometry with multiple self-intersections.
Given a point that sits on a multiple self intersecting vertice of this linestring, I'd like to have an output like ...
- 19
1
vote
0
answers
47
views
Compute the distance between two line endpoints with intersecting linestrings
Assume that we have three line-segments, each represented as linestrings in PostGIS.
What I intend to compute is the distance between y and x. What I am doing is as follows:
with recursive len_rec as (...
- 111
0
votes
0
answers
80
views
PostGIS S3 Out-DB raster: Cannot open offline raster
I want to import and query a GeoTIFF stored in an Amazon S3 bucket via PostGIS as an out-DB raster.
The file was imported via raster2pgsql using GDAL's Virtual File Systems interface, i.e.
...
- 21
2
votes
2
answers
51
views
ST_Touches for more than one geometry again
I do have a similar problem as described in Stack Exchange article ... the whole story is a bit complex: what I really want is to create polygon-skeleton with ST_ApproximateMedialAxis but this one ...
- 840
1
vote
1
answer
40
views
PostgreSQL/PostGIS table of lines / spatial join to polygons
I'm pretty new to PostgreSQL/PostGIS.
I have a (very large) set of records in a PostGIS enabled PostgreSQL database. It has two lat/long points per record, which I've used to define the spatial ...
- 11
1
vote
0
answers
56
views
ST_MakeValid for MultiSurface type in PostGIS
I am trying to take a lot of overlapping (bullseye type shape) polygons and curve polygons and create a valid shape with the correct cutouts. I was previously using a query that converted the ...
- 19
1
vote
1
answer
98
views
ST_Intersects AND ST_Touches combination is much slower with large geometry input
I am trying to geom columns from multiple table and later combining with a table which only has linestrings, I am finding lines which are not intersecting OR just touching the union of geom columns. ...
- 23
0
votes
0
answers
32
views
Translate a point until it reaches surface PostGIS
I've tried a few different ways to extend my lines (4326) out by 300 meters on each end (straight lines with only 2 vertices). One way was to buffer each end point by 300 meters (since st_buffer can ...
- 526
2
votes
1
answer
45
views
Remove parts of line that are not contained within multiline
PostgreSQL 14.6, Postgis 3.2
I have two tables, new_geom and original_geom
new_geom is a derivation of original_geom that merged some lines that touched and had the same properties. It worked fairly ...
- 295
1
vote
1
answer
54
views
PostgreSQL - Geography shape becomes invalid
I have a geography that looks like this in geojson.io:
I have a geography column in DB, which stores the above geography that looks like this in pgAdmin:
As you can see in the pgAdmin image, it has ...
- 111
1
vote
1
answer
40
views
mapnik-renderd error: FATAL: role "_renderd" does not exist
mapnik-renderd error: FATAL: role "_renderd" does not exist
I've been following this tutorial to making an osm tile server on Ubuntu 20.04 (WSL), but after restarting the renderd service ...
- 21
1
vote
0
answers
39
views
Enterprise geodatabase connection feature geometry invisible in ArcGIS Pro
I have a database connection made to my PostgreSQL enterprise geodatabase through ArcGIS Pro. I can not see the geometry of the layer through ArcGIS Pro.
I am connected to the database as a user other ...
0
votes
1
answer
93
views
How to create a blank PostGIS database without installing PostgreSQL (portable)?
I use computers at my university. I cannot install programs on these computers. I don't have PostgreSQL installed on these machines.
I need to create a geographic database, as I need to edit very ...
- 432
1
vote
1
answer
40
views
GeoNetwork (Map mode) : Load a PostGIS table directly?
With GeoNetwork, within the map mode, is it possible to load a PostgreSQL/PostGIS table directly?
For instance, using a TCP/IP protocol (or another way), or we are obliged to load GIS data via a web ...
- 839
0
votes
1
answer
40
views
Generating thumbnail from PostGIS table link directly
With GeoNetwork, I know it’s possible to create a PostreSQL/PostGIS link with metadata.
But from to this PostGIS acess feature, is it possible to generate a thumbnail? (like for the WMS protocol)
- 839
1
vote
2
answers
44
views
osm2pgsql error "no COPY in progress" when importing map data into PostgreSQL
I've been trying to import map data into PostgreSQL 12 database (on Ubuntu 20.04.5 with wsl) using osm2pgsql, but I get this error after executing this command:
$ osm2pgsql -U postgres -H localhost -...
- 21
3
votes
1
answer
38
views
Cutsom ST_Multisplit function returning more than one split line. How to only return one line split at various points?
I Have a modified version of the function from How to ST_Split features in one table by features in another?
CREATE OR REPLACE FUNCTION ST_Multi Split(geom Geometry, blades Geometry) RETURNS Geometry ...
- 75