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
1
vote
2answers
55 views

Store Multiple Features of FeatureCollection in a postgres geography field

I am working on GO Echo project and we connect to a postgres database running postgis. I am getting the following json into my rest api { "type":"FeatureCollection", "features":[ { ...
0
votes
1answer
42 views

Alternate of intersects ?# operator in postgres

Is there any way to find if a path is intersecting with a box in postgres without using ?# or using an alternative? I have this query below and hibernate's EntityManager is throwing an exception ...
0
votes
0answers
30 views

Raster2pgsql turning raster values from Float32 to Byte

I would like to add netcdf files to a postgreSQL database. I'm running the command: raster2pgsql -C -I -F -s 4326 test.nc | psql -h localhost -p 5433 -U postgres -d UV It adds them successfully but ...
2
votes
2answers
302 views

Using PostGIS to generate a polygon representing the area shared by some (but not all) of a set of polygons

I have an application where several users submit polygons, and I am looking to create an aggregate polygon that represents a kind of average of their submissions. We use PostGIS for our spatial ...
1
vote
1answer
43 views

Replacing WKT with WKB in PostGIS query & Shapely

I am extracting a geometry (type geometry(Geometry,4326)) from a PostGIS database. I managed to plot them from a GeoPandas GeoDataFrame using the following code : cursor.execute("SELECT id, ...
0
votes
0answers
30 views

Making map with Python from FGDB or GML or GPX or PostGIS or SOSI and TIFF files

I am a complete beginner in GIS domain and I would say intermediate level with Python. I would like to use Python to plot a simple map displaying the hiking trails of Norway to prepare my future ...
1
vote
2answers
33 views

How km column is computed in osm2po output table

I've generated hh_2po_4pgr table using osm2po utility. Now I want to add a few extra edges by hands because some important points are missing from source osm data. In order to do this I need to ...
1
vote
1answer
63 views

Find kNN having reference in a table

I am working with PostgreSQL 12.2 and PostGIS 3. I have been through a lot of posts talking about "Lateral joins" and other solutions but I am stuck with some incoherent results up to now. I have ...
0
votes
0answers
55 views

Performance difference on ST_DWithin between tables with similar size

I am performing a spatial join between the left table (at_test) with about 1000 rows and two right tables (sociodemo_filt and pb_poi_geog) with about 5M rows each. I am working with geographies, and ...
4
votes
2answers
59 views

Join is working in pgAdmin but not in DB Manager

I have two tables tbl1 and tbl2 without geometries which I'm trying to join (and later join result to a table with geometries when I get the first join to work): Simply: select * from tbl1 left join ...
1
vote
1answer
39 views

How to remap a raster to its min / max values in PostGIS?

I have a table of 16-bit signed (!) integer heightmaps (SRTM tiles) loaded into PostGIS. This website showed me how to export a raster as a PNG to a file: COPY (SELECT encode(ST_AsPNG(raster), ‘hex’) ...
1
vote
0answers
61 views

Finding nearest POIs by travel time(not distance) given current location and travel method

A user can choose between walking, bicycling, public transit, or driving for X minutes. Their current location is a latitude and longitude pair given by the HTML5 Geolocation API(wgs84). The (roughly ...
0
votes
0answers
42 views

Install shp2pgsql without PostGIS

I have Django app that handles an uploaded shapefile and extracts the geom using shp2pgsql. As the Django app and the PostgreSQL(PostGIS) are in different docker containers, I cannot call shp2pgsql ...
3
votes
1answer
207 views

Upload multiple tables from a single Geopackage file to PostgreSQL (PostGIS)

Context I have a single GeoPackage file containing several layers (tables) with different geometry types that I want to push to a PostgreSQL (+PostGIS) database. Each table in the GPKG has a unique ...
1
vote
1answer
36 views

Gdal ogr2ogr update a PostGIS table without breaking views and relations

Context I'm trying to update a PostgreSQL+PostGIS table from an ESRI Shapefile using the ogr2ogr too from gdal. The Shapefile is being updated from external sources on a regular basis. I don't want ...
1
vote
1answer
62 views

Making PostGIS extension persist in the search_path of a PostgreSQL database?

Context I have a script to set up a PostgreSQL database along with the postgis extension which I decided to install in an other schema than public, namely, the postgis schema. In my script, this ...
0
votes
1answer
26 views

Why is ST_X and ST_Y of recently inserted GEOGRAPHY(Point) for certain coordinates different than value inserted?

I have a table of cities that includes the name and location of that city. CREATE TABLE cities ( name varchar (255), location GEOGRAPHY(Point) ) For certain cities, I'll set the ...
7
votes
4answers
416 views

Count number of points by name within distinct polygons [closed]

In PostgreSQL/PostGIS I have 2 tables: A POLYGON table (polytable1) with shapes named 'Poly 1', 'Poly 2' etc... Each name can appear multiple times (i.e. 20 polygons with the name 'Poly 1') A POINT ...
1
vote
0answers
37 views

Executing multiple PostGIS functions in a single query

I am brand new to PostGIS and this question is probably just due to my limited SQL experience, but what is one way to execute multiple functions sequentially in PostGIS? For example, I have vector ...
0
votes
0answers
16 views

Find modified rows in two GeoDataFrames having split features

I have 2 GeoDataFrames having features that are essentially LineString and they are identified with a feature_id column. The first GeoDataFrames consists of the original features, while the second ...
2
votes
1answer
53 views

PostGIS: Finding duplicate label within a radius

I have data in PostGIS that have value and geometry. If there is a same value within let's say <10 m, I want to detect or remove that value from my table. Here is the small example: create table ...
0
votes
1answer
27 views

Local OSM server query

I need to make a query for my local OSM server which returns a table with 2 columns: user.display_name and the total of nodes of the ways with tag k='waterway' created, edited or deleted by that user. ...
0
votes
0answers
29 views

Import a shapefile into PostgreSQL using npgsql (.NET)

I want to import a shapefile (.shp) into a PostgreSQL database through a .NET application. I plan to use npgsql but I am unable to figure out how to import a shapefile using this library. PostgreSQL ...
1
vote
0answers
35 views

An error IOExceptionERROR: parse error - invalid geometry Hint: “POLYGON ((” <— parse error at position 12 within geometry

I'm a beginner in GeoServer. I moved a layer using the PostGIS (version 3.0.1) module in QGIS (version 3.12.1-București) to the PostgreSQL 12 database without problems and it looks good in pgAdmin 4. ...
4
votes
2answers
81 views

Select points falling outside of buffer and count

I am currently learning PostGIS and following the course offered by Datapolitan. I want to do the steps mentioned in slide 200 https://training.datapolitan.com/qgis-training/Intermediate_GIS/#200 ...
2
votes
1answer
66 views

Enclose polygons that are not overlapped and remove gaps between them?

I have to create a big polygon with the outer ring from some selected polygons that are close to each other like in the picture. Is it somehow possible two fill the small gap between the polygons as ...
0
votes
0answers
32 views

QGIS 3.12: Importing shape (point) to PostGIS database as points without Z values

Currently I am importing vector layers (points) to a PostGIS database via QGIS 3.12. Everything works great expect for one part: after the import is complete all my points have a Z value (which is ...
0
votes
1answer
33 views

Upload a Geopackage to PostGIS-DB fails

I'm trying to load a relatively big Geopackage into my database with ogr2ogr and Windows Subsystem for Linux: ogr2ogr -f PostgreSQL "PG:user=postgres password=#### dbname=db host=localhost port=5432" ...
0
votes
2answers
32 views

Convert PostGIS geometry column to a vector of Point2D in a PostgreSQL extension

I'm writing a PostgreSQL extension that should do a certain mathematical operations on a geometry field. However all I really need to make it work is give it coordinates as std::vector<Point2d>. ...
0
votes
0answers
22 views

Write a PostgreSQL extension that makes use of PostGIS internal methods?

As I've just found out, it is possible to extend PostgreSQL with custom functions written in C. And I also now know that that's how PostGIS itself is written. However what if I want to build on top of ...
3
votes
1answer
91 views

SQL Syntax Error in QGIS near 'DELETE' [closed]

I can't understand what is wrong in my syntax, or if this is specifically an issue with QGIS but I am hoping someone can explain why I am still receiving a syntax error for this. I am trying to ...
0
votes
0answers
25 views

How can have access to the inner features (neighbors, edges) of a triagulation remove points from the mesh under PostsGIS

I've a calculated a triangulation of a point set grouped in certain distance (buffer). For areal classification purposes, I need to calculate: (a) the points in the unified buffer, which is easy (b) ...
1
vote
1answer
59 views

Spatial Index for lat/lng on JSONB column

I have a table with thousands of records with a JSONB column that has a location (geometry) as a point. I am able to select points within a bounding box using this query. SELECT url, ...
1
vote
0answers
55 views

Adding number for each linear feature group by type using PostGIS

I have a linear layer with line type A,B,C (red,green,blue). For each linear feature I added MANUALLY sequence numbers (for line with name A: 1,2,3,4,5; for line with name B: 1,2,3; for line with name ...
4
votes
3answers
183 views

How to group by / Order by with logical sequence

I have a problem in SQL (QGIS). I have a shape with plots. I would like to obtain a certain organisation. Here a schema of what I have and what I want :
1
vote
1answer
37 views

Unable to load raster data from PostGIS database into GRASS GIS

I have a PostGIS (PostgreSQL 10.8) database with vector and raster data that I have connected to GRASS (7.8). I have successfully loaded my vector data into GRASS from the PostGIS database but have ...
2
votes
1answer
61 views

ST_Union 'MIN' on rasters with result raster having DN values as raster table column value

I am new to working with rasters in PostGIS. Although I have spent couple of days trying to find solution for my problem, I couldn't seem to crack it without some help. Here's my problem: I have ...
1
vote
1answer
50 views

Why can't QGIS connect to remote PostGIS installed on an Amazon-Lightsail VM?

I've setup a local installation of PostGIS in the past, and had no problem connecting QGIS to it in Localhost. However, I recently rented an Amazon Lightsail Ubuntu virtual machine to make my PostGIS ...
0
votes
0answers
36 views

Problem with importing VFK into PostgreSQL with ogr2ogr

Problem is importing VFK (Czech cadastrial data format) into PostgreSQL by ogr2ogr. Versions are: PostgreSQL: 12 PostGIS: 2.5.3 GDAL: 3.0.2 Command is: ogr2ogr -overwrite -f "PostgreSQL" PG:"...
3
votes
2answers
170 views

What exacly ST_SetSRID in PostGIS does?

I am new to DBA and PostGIS. I am developing an application using PostgreSQL and PostGIS where nearby locations need to be queried. I created a sample table as below: CREATE TABLE foo (geog ...
3
votes
1answer
149 views

Use PostGIS functions from an other schema (than public)

Context I have to set up a postgresql database with several schemas. To separate things, I decided to install postgis elsewhere than the public schema, based on https://postgis.net/2017/11/07/tip-...
1
vote
1answer
58 views

Converting a text (lat/lon) to a point or geography

In my table I have a column filled with text type lat/lon entries. I want to convert these entries to a point? Or something I can use with PostGIS functions I am aware of SELECT ST_PointFromText('...
1
vote
0answers
27 views

Problem with adding new layers with EPSG:404000 in Geoserver

I'm using a Postgres database (with a PostGIS extension) connection in Geoserver to add new layers. When I add a new layer from this connection, in the Coordinate Reference Systems Section, Geoserver ...
0
votes
1answer
75 views

Import OS Mastermap to PostgreSQL database

I have ordnance survey master map a coverage for the whole of the UK. These are currently received on a disk every 3 months and arrive in .gz format. Does anyone have any recommendations on how to ...
1
vote
1answer
40 views

How to extract attributes of polygons at specific points into new point layer in PostGIS?

I am not very practiced in PostGIS. However, my volume of data forces me to use it. I have a large polygon layer containing several million polygons and a grid layer that has a point every 10 meters ...
1
vote
1answer
28 views

“Vertex Tool” only for some PostGIS Layers

I want to edit PostGIS layers within QGIS and before that, I changed a layer using ST_Difference. Now, I can select QGIS' Edit Feature after importing it from PostGIS but only for some layers it is ...
3
votes
1answer
57 views

I have two geometries that should be identical but they're not. Where is the difference?

I have two geometries that appear identical when I use st_asewkt, but ST_Equals returns false. SELECT st_asewkt(tb1.geom), st_asewkt(tb2.geom) FROM (SELECT '...
1
vote
1answer
73 views

Return rows where distance is within X meters

So how do I write a query to get all rows within X meters of a certain lat/long coordinate? I tried looking into the docs, but it's super confusing, ideally I just want to write SELECT * FROM ...
1
vote
0answers
63 views

shp2pgsql drop database table option doesn't work if table doesn't exist yet

Context I have a PostGIS database. If I use: shp2pgsql -I -s 4326 -d "my_shapefile.shp" myschema.vectordata \ | psql -h localhost -U username -d my_database -v ON_ERROR_STOP=1 To inject data ...
0
votes
0answers
42 views

Pull JSON array (from Overpass API) to PostGIS?

I would like to integrate OSM data into my Postgresql/PostGIS database automatically (trigger-like operation) using Overpass API. For my tests I have selected the following query: https://www....

1
3 4
5
6 7
151