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
0
votes
1answer
15 views

How to get the file location of rasters registered with the PostGIS extension?

Using raster2pgsql I am able to load a NetCDF file into PostgreSQL using the PostGIS 3.1 extension. And looking at the documentation I can see that it's possible to register a raster file instead of ...
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 ...
-1
votes
0answers
9 views

Integrating documents (PDF / Images / videos / sound)

I am in need of integrating multi-source information on my GIS project. Part of it are normal tables and other content will be images, drawing, videos, PDFs, etc. All tose elements are to be ...
0
votes
1answer
23 views

ERROR: more than one row returned by a subquery used as an expressionSQL state: 21000 Why and how it happens?

I want to update an empty column with a join : UPDATE icpe_modifie SET region = (SELECT nom_reg_m FROM icpe_modifie as icpe JOIN regions as reg ON ST_WITHIN(icpe.geom, reg.geom)) icpe_modifie is a ...
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 ...
7
votes
3answers
215 views

PostGIS: Interpolate/Cluster geographic points to average points

Setup: PostGIS 3.1, QGIS 3.16 I have a bunch of geographical data (geometries). They show more or less the same route, just measured several times. Due to the many measurements and the GPS precision, ...
3
votes
2answers
57 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 ...
0
votes
0answers
25 views

Get PostGIS geometry viewer route drawing

I am using pgrouting pgr_dijkstra function to calculate the shortest path between two points. Sorting the result by "seq" gives me the correct sequence of the path to my destination. In the ...
0
votes
0answers
15 views

Trigger converting multilinestrings to curves

I created a small web app based on PostGIS / Geoserver / Leaflet. I can draw polylines from my interface thanks to the Leaflet Draw plugin and send them to PostGIS with a Php and Ajax script. When my ...
0
votes
1answer
24 views

Getting multipolygon vertexes to points in PostGIS

I want to extract the coordinates of the vertices of a polygon, and then create a point on each of the vertices. Sample of Polygon geometry table (multipolygon): Wished output:
1
vote
1answer
46 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 ...
-2
votes
1answer
50 views

GeoJSON coordinates are displaying incorrectly on map

I am setting up my first Leaflet map with react / node / PostgreSQL and have managed to get to the level where I have pulled the geojson form my PostgreSQL database. However from what I can see the ...
1
vote
1answer
32 views

Optimising PostGIS ST_DWithin across many tables for existence only

I have 13 tables containing MultiPolygons in the same projection (27700). I am trying to write an efficient query to get back a Boolean for: Is one polygon in table A within X meters of any polygon in ...
0
votes
0answers
18 views

Tile server fail to display map

I ma begining with vector tiles and geo data in general. I am running a T-rex server to serv data post a postGIS database. i have an issue with my browser part because i can't display anything ! ...
1
vote
0answers
17 views

Calculate cumulative distance for route using PostGIS

The route shown below represents the mobile signal strength for a route thats been driven. The red dots are poor signal, yellow dots fair signal etc. The data is stored in a postgres table and is geo-...
0
votes
0answers
20 views

Connection database failed in Qfield

I'd like to take a test about the connection to the database in QField in my smartphone Android but it failed; so I cannot view the layer Postgis on the map. The database Postgresql is installed and ...
1
vote
0answers
22 views

What CRS is my data being transformed to in ST_Project? What about ::geometry?

My understanding is that ::geography transforms data from whatever CRS into 4326. But what is ::geometry doing? Relatedly, in the context of the ST_Project function: st_project(a.geom::geography,b....
0
votes
0answers
45 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
78 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 ...
0
votes
0answers
23 views

QGIS/PostGIS finding shortest route going through all the polylines at least once

I have a QGIS 3.10 map with two main PostGIS 3.0 layers: Zones (Polygons): the town is divided in 26 zones. Each municipal street sweeper only works inside his zone. Street lines (Linestrings): These ...
1
vote
1answer
29 views

Exporting many tables from PostgreSQL to GDB using ogr2ogr

So basically I imported .gdb package into PostgreSQL with the following command: ogr2ogr -f "PostgreSQL" PG:"dbname='my_database'" "ntest.gdb" "layername" -...
0
votes
0answers
22 views

Different results for GEOGRAPHY and ST_TRANSFORM query on geo_point field

I am finding properties that are within 20K distance using ST_DWithin. But when I try with ::GEOGRAPHY casting the result is different from ST_Transform. Note: SRID of geo_point field is 4326 Indexed ...
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
57 views

Drawing a buffer from a POINT and a value for Acreage

We have a situation where we get a single lat/long point and a value for "acreage", and we need to draw a polygon circle that is roughly the size that the acreage value implies. What we have ...
1
vote
0answers
36 views

How to “split” a single MULTILINESTRING into multiple MULTILINESTRINGs that intersect?

I'm trying to solve this problem using the R sf package, but I assume that an answer in PostGIS could also help me get to an answer in R. Basically, I have a shapefile with the following columns: &...
1
vote
2answers
48 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
140 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
39 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
1answer
19 views

org.geotools.jdbc.JDBCDataStore change column name from the_geom to shape?

org.geotools.jdbc.JDBCDataStore stores the geometry for a shape file in a table column called the_geom. Is it possible to change the name of this column to something other than the_geom, perhaps shape ...
2
votes
1answer
38 views

Counting categorized points in polygons using PostGIS

I want to do an operation i PostGIS similar to the count points in polygon in QGIS. I have A multipolygon table (plan.polygon) consisting of several areas A point table (basemap.points) in a PostGIS ...
1
vote
1answer
47 views

Transform coordinates [duplicate]

I have two shapes, one called bike_stations and another one called quite_streets, both contains geographic information about elements in Madrid, Spain and each one come from two different sources. The ...
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
64 views

Calculate Distance between 2 Shapely Points in Meters

I am trying to calculate the distance (in meters) between 2 lon/lng points of type shapely.geometry.point.Point using Python and Shapely. These 2 points are returned from PostGIS by SQLAlchemy/...
2
votes
1answer
33 views

Change PostGIS Column from Geometry to Geography

I have a PostGIS table with a column of type Geometry(POINT) and SRID 0 that must be changed to the type Geography(POINT) as it will be used to store lat/lon values. How can we make this alteration to ...
0
votes
1answer
21 views

Multiplying rasters by rasters within PostGIS

I have a situation where I have to multiply sets of rasters by each other. Currently I have subfolders of rasters on my machine and am looping through them and multiplying them with rasterio. Is there ...
1
vote
1answer
27 views

Function st_clip(raster, integer, geometry) does not exist

I am trying to replicate this example by alphabetasoup for summarizing pixel values according to census polygons, but am getting this error. ERROR: function st_clip(raster, integer, geometry) does ...
3
votes
0answers
23 views

Adding points to every “eligible” cluster in PostGIS (ST_ClusterDBSCAN)

I'm currently using ST_ClusterDBSCAN in PostGIS to identify similar points for conflation. (Using clustering to see where multiple entries are within a given range and then manually reviewing & ...
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
55 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
1answer
35 views

Is this ST_Relate result expected?

The following PostGIS query involving ST_Relate gives FFFFFF102 as its result: SELECT ST_Relate( ST_GeomFromText('POLYGON EMPTY'), ST_GeomFromText('MULTILINESTRING((0 0,0 1),(0 1,1 1,1 0,0 0))'), ...
2
votes
0answers
38 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 / ...
2
votes
0answers
16 views

How to use WHERE IN clause with ogr2ogr [duplicate]

I am exporting some tables from PostGIS to a geopackage. My command to do is task is as follows ogr2ogr -append -f GPKG E:\workspace\test.gpkg PG:service=XXXX -where app_einzugsgebiet.hauptgemeinde=...
0
votes
1answer
17 views

Querying PostgreSQL DB with Google “get.bounds()” coordinates

I am trying to only display markers on a Google Map that are in the current map bounds and then update the markers when the map moves. The difficulty I am running into is querying my database to pull ...
1
vote
1answer
52 views

Split line across multiple polygons preserving order

I have polygons stored in the database as (Multi)Polygons using PostgreSQL (13.1) and PostGIS (3.0.2) with a srid of 4326 (aka states). A user generated line (LineString) (aka route) will be used to ...
0
votes
1answer
34 views

Using SQL View as ImageMosaic Store index in GeoServer

I have an application server creating rasters from the vector data it receives periodically. The data contains either "real" measurements or forecasts about a specific weather phenomenon, ...
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
38 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
65 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:(*"...