Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
-1
votes
1answer
26 views

Handling a database with more coordinate points than I need to be returned

I have a database with millions of coordinate points and I need to select a subset of them to create a heat map based on a bounding box. There are so many points that it's hurting performance on my ...
1
vote
1answer
18 views

How to write docker-compose.yml after enabling postgis_raster for PostGIS Docker Image

I'm using the docker image of https://github.com/postgis/docker-postgis and it works fine with the commands below which is also defined in README file. docker run --name some-postgis -e ...
1
vote
1answer
20 views

pgrouting/PostGIS Trying to set nearest node to airports in a region brings the same node for each airport

I need to make a distance matrix from all the nodes in the Canary Islands to every airport in the region. I'm following the method set here but it brings the same node for all airports. Trying the ...
1
vote
0answers
11 views

Snap lines (map matched trajectories) to road network in PostGIS

Using OSRM I have map matched a series of GPS points to a custom road network. My goal is to provide insight in where but more importantly how many times the vehicle has visited each road segment in ...
1
vote
0answers
20 views

pgRouting - Assigning source and target from different point layers (AOIs) for network analysis

I'm new to pgrouting and normally use ArcMap to conduct any sort of network analysis. I have created a simple shortest path network along a downloaded OS Mastermap road network using pgRouting. ...
0
votes
0answers
12 views

Import raster data type from 'PostgreSQL bytea field' to raster layer in QGIS

I have a table with some images into Binary Data Types field of PostgreSQL (bytea). I want to import them into a QGIS layer but the program does not recognize the fields. What I can do?
4
votes
2answers
83 views

Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data

I imported a shapefile with 3000 polygons in UTM 25832 into both databases MS SQL Server 2019 and PostgreSQL/PostGIS. Then I calculated a buffer of 1000 meters around these geometries and transferred ...
1
vote
0answers
26 views

Import OSM with osm2pgsql - missing features

My problem is missing features in planet_osm_polygon table. In the planet_osm_rel table I can find Sahara desert - it means that relation should exist, but in the planet_osm_polygon table I can't find ...
0
votes
0answers
27 views

PostGIS Geography Data Changed After pg_restore

Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using a Docker image kartoza/postgis:13.1. A dump of the database was ...
0
votes
0answers
19 views

pg_tileserv SET DATABASE_URL return

I'm trying to set the SET DATABASE_URL for pg_tileserv in the cmd terminal using: DATABASE_URL=postgresql://postgres:[password]@127.0.0.1:60999/pgr which seems to be accepted. But when I start ...
0
votes
0answers
52 views

ogr2ogr TopologyException invalid input geometry; ring self-intersection

I'm facing a problem when using osgeo/gdal:ubuntu-small-3.2.1 docker image and running these commands on the Natural Earth vector dataset: # Get and unzip the data # (inspired from https://github.com/...
2
votes
0answers
34 views

Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS

I have two shapefiles: One point file (occ_master) and 1 polygon file (geol_1m). The point file represents mineral occurrences. The polygon file represents geology, which can be subcategorized by ...
1
vote
0answers
10 views

Using ST_snap to snap pairs of points

I am using the following script to snap a set of point to another within a certain distance. The script works but there are some pairs of points that don't overlap even though the distance between ...
1
vote
1answer
28 views

Upload specific or all tables from Geopackage to PostGIS database

I would like to upload a specific table (i.e. U2018_CLC2012_V2020_20u1 (Multi Polygon)) from a Geopackage file to a PostGIS database from the command line. I tried: ogr2ogr -f "PostgreSQL" &...
2
votes
0answers
20 views

Hiding info from layer panel mouse over

I have QGIS linked to PostgreSQL but when I add layers from the db onto the project, people can easily see the user by simply mousing over the layer (which is the master user) so I need to hide it. ...
0
votes
0answers
12 views

failed to load dom raster via raster2pgsql

I want to load a raster file (dom) to postgis via raster2pgsql. I used C:\Programme\postgresql\11\bin>raster2pgsql -s 25832 -I -C –b -t auto –R D:\gis\dom.tif public.dom_test| psql -h IP -d ...
0
votes
1answer
25 views

How to use postgreSQL to store user credentials in GeoServer?

Is it possible in GeoServer that instead of using .xml to save all user’s credentials can I use PostgreSQL, and then use those credentials to login? PS: I tried JDBC driver, but not working. I can see ...
1
vote
0answers
22 views

Create a PostgreSQL read-only profile to prevent users from modifying data in QGIS

I would like to create a specific profile (a user/role?) on one of my PostgreSQL server databases. QGIS users (connected to the PostgreSQL database) should only be able to view the data and not modify ...
1
vote
1answer
20 views

Import of shape in postgis failed

I tried to import a building-shapefile (ax_gebaeude) into the postgis database (Postgresql 11). I normally use Qgis 3.12 with drag & drop for that. In this case I get an error message: Failed to ...
0
votes
1answer
20 views

Ogr2ogr importing table with all in text type - how to change geometry accordingly?

I've imported a large table that has a geometry column, problem is that it is in text format, how to change this to be a point geometry in 27700 CRS? I've tried SELECT UpdateGeometrySRID('table','geom'...
0
votes
1answer
51 views

Which coordinate reference system should I use to have more precision while calculating polygon area? 4326 or convert to the one specific to my area?

We are saving our drawn polygons in our database with the coordinate system 4326. The polygons drawn are all in Spain (where I came to find out the adequate coordinate system for Spain is EPSG:2062). ...
1
vote
0answers
60 views

QGIS + PostgreSQL + row-level-security + SET ROLE in multi-user environment

Is it possible to execute SET ROLE from within QGIS? What I want to achieve is to create individual roles for users, that should have access to the same part of data within tables, via existing RLS ...
1
vote
1answer
30 views

Deriving x, y (and z) parameters for ST_TileEnvelope from `Geometry` data in table and generating vector files

This is a update 1 thus far on the advice in the comment: It looks like I need ST_ConvexHull to create the overall bounds of all of the geometries. It's tested against the individual geometries using ...
2
votes
0answers
34 views

osm2pgrouting ERROR: relation “__ways****” does not exist

I'm using osm2pgrouting. It works for small datasets very well but when I run it on a large dataset, it start good but after some processing like 40% it starts giving following error [*****************...
0
votes
0answers
23 views

Using ogr2ogr to convert a DXF to GeoJSON and place in PostgreSQL table

Is it possible to use either the ogr2ogr command line to both convert a DXF file to GeoJSON type and put into a PostgreSQL table at one time? e.g. ogr2ogr -f "GeoJSON" path/to/output.json ...
0
votes
0answers
22 views

pgAdmin 4: create view without defining schema

I have a database containing the official cadastral surveying of hundreds of counties. For every county I have created a new schema named state_zipcode_countyname. As all my db entries have the same ...
0
votes
0answers
34 views

Run ST_DWithin on a subquery result from a different table

I have two tables "Customers", and "Providers" in my PostgreSQL DB. They both have a 'geom' column. Now, customer Table has a column called e.g. "profession". I want to ...
3
votes
2answers
54 views

PostGIS- slow spatial UPDATE queries on 50mil rows

PostgreSQL 11.8, AWS hosted RDS instance, SSD, 8GB RAM, 3.0 GHz Intel Scalable Processor. Plenty of storage space. I'm finding running updates on a large table using spatial queries is taking a very ...
1
vote
1answer
44 views

How to load valid PostgreSQL QgsVectorLayer

Note: This is NOT a closed question. The following links does NOT provide an answer to the solution: Add PostGIS layer to QGIS via Python Console The Vector layer still remains invalid with the ...
1
vote
1answer
27 views

PostgreSQL export geometry to .sql file [closed]

I don't know how to export the geometry of tables in a .sql format from PostgreSQL database. How can I do this?
0
votes
0answers
42 views

PostGIS latitude/longitude vs longitude/latitude confusion

I have been importing polygon data on PostGIS but started to wonder if I had input latitude/longitude in the right order. On PostGIS doc there is a page https://postgis.net/2013/08/18/tip_lon_lat/ ...
1
vote
1answer
77 views

PostgreSQL - Merge only lines that share a specific attribute

I have a trail that is split up into multiple small parts. On some of the parts i have managed to get the traile name as attribute "text". The rest has text as "null". Now I would ...
1
vote
0answers
14 views

Postgresql - Snap intersecting and broken MultiLineString

Im trying to use Postgresql to fix a few thousand hiking trails that i have in a map source. Now i have all of the trail parts as MultiLineString but my issue is that each of the trails are broken ...
3
votes
1answer
89 views

PostgreSQL + PostGIS count with ST_DWithin multiple distance at once

I am using ST_DWithin to count rows that are within 5KM, What I want is count on multiple distance at once with single query i.e count for 5KM, 10KM, 20M Current Query SELECT count(*) AS cnt FROM ...
1
vote
2answers
47 views

SQL select features in a polygon

I have the following code which works fine: select vissoort, count(1), ST_Buffer(ST_GeomFromText('POINT(5.341248 51.615590)',4326):: geography, 2500) from visvangsten where st_intersects(...
3
votes
3answers
139 views

How to import GDB to PostgreSQL into separate tables?

I am trying to upload a .gdb data into my postgres database using og2ogr. If I upload whole database by command ogr2ogr -f "PostgreSQL" PG:"" "test.gdb" -nln gdb_test -...
0
votes
1answer
38 views

PostGIS: creating a raster with cell value counts from another raster in a buffer around each cell

Main objective My main objective is to compute 1) the proportion of a given type of land cover around every raster cell in a radius of 500 m at the scale of an island (e.g.: there is 40 % of ...
1
vote
0answers
38 views

Extend PostGIS polygon with 2 new points

I have a PostGIS polygon which has to be extended using two points like on the picture: My original polygon is: POLYGON((10 20,10 10,20 10,20 20,10 20)) The points I would like to extend the polygon ...
0
votes
0answers
28 views

PostGIS ST_Intersects or ST_Within for Point in Boundary

When I query with ST_Intersects or ST_Within I get the same result. I want to know which one to use for performance wise? Note: For points, there is no difference. Using ST_Within: EXPLAIN ANALYZE ...
0
votes
0answers
17 views

Visualizing raster files stored in postgresql DB in Openlayers or Openstreetmap Layers [duplicate]

I am relatively new to this domain. I want to make a web app that will show raster data on openstreetmap layers or openlayers. So far I have successfuly managed to upload the files to postgresql. My ...
1
vote
1answer
65 views

Counting Polygon Centroids in Polygons

I have two tables in PostgreSQL 13: hexbin table with geom field (red) sites table with geom field (blue) I want to return a table with the hexbin geometries and a total count of where the sites ...
0
votes
0answers
50 views

GeoServer: Cannot create PoolableConnectionFactory

I am trying to add a PostGIS database a data store in my GeoServer installation but I keep getting this error: Error creating data store, check the parameters. Error message: Unable to obtain ...
0
votes
2answers
22 views

How to seralize ArcGIS JSON to .Net core object?

I want to get ArcGIS JSON response (like here) from server and save it to my database. I am using .Net core and entity framework net topologysuite for CRUD operations database. But I coud not convert ...
2
votes
1answer
69 views

Calculate 500m south of coordinate in PostGIS

I have a point layer punkte_test. From that I want to make a line: first point of the line is the point A from the point layer itself. Second point of the line shall be y(B) = y(A) - 500 m . How can I ...
0
votes
1answer
48 views

python - psycopg2.errors.RaiseException find_srid() - could not find the corresponding SRID

I'm trying to import a layer into a PostGIS database with gdf.to_postgis('test_polygons', engine, if_exists='append', index=False, dtype={'geom': Geometry(geometry_type='POLYGON', srid= 4326)}) And I'...
2
votes
0answers
37 views

GeoJSON format (Geography) for posting in Postman tin PostgreSQL

I am trying to do an update posting on Postman for the first using PostGIS / Geography file type and I don't know where I am going wrong. I have included the model below of the table in pgamdin 4 / ...
0
votes
0answers
17 views

Postgresql POSTGIS not using index field while having GIST index on geometry field [duplicate]

I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq Indexed ...
1
vote
0answers
37 views

PostGIS difference between polygons - Ring Self-intersection?

I want to take ST_Difference or ST_Intersection to take the difference between two polygons. SELECT a.id, a.geom, st_difference(a.geom, b.geom) FROM liechtenstein.flaechen_norden a, liechtenstein....
1
vote
1answer
64 views

PostGIS function st_makevalid crashes the PostgreSQL Server [closed]

When I run my code running on PostGIS version 2.5, I don't get an error but when I run it on 3.1 it the server gives an error and enters the recovery mode every time. pgadmin4 messages:(*"...
1
vote
0answers
39 views

shp2pgsql available in postgis:13-3.1-alpine but no in postgis:13-3.1 docker image

From the postgis/postgis:13-3.1-alpine docker image I can push 3 shapefiles into my database using shp2pgsql in an init script. But from the postgis/postgis:13-3.1 image I cannot: (...) db_1 | /docker-...

1
2 3 4 5
71