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
5
votes
4answers
276 views

Finding truly intersected polygons

Intro Short version: My goal is to find geometries intersected by a target polygon. To find overlaps - the most straightforward choice is to use ST_Overlaps or ST_Intersects. But this method will ...
0
votes
1answer
47 views

Change CRS of layer, but keep location on map?

I have some layers I've inherited. They are all polygons. Most of them are in the correct CRS, which is EPSG 4326. Some of them are not, they are in EPSG 26918. They were natively created this way. ...
1
vote
0answers
20 views

excluding KMZ layer in ogr2ogr PostGIS import

I'm trying to script the import of a KMZ file into my PostGIS 2.2.1 enabled PostgreSQL 9.5.1 database and am running into a problem where it's not importing all of the (several dozen) layers. I think ...
2
votes
0answers
20 views

Load domain values from .xml when creating PostGIS database with ili2db from .ili

I'm trying to create a PostGIS database with ili2pg. This creates a proper database schema. There is an additional .xml file with domain-values. I think these values aren't loaded in the process. How ...
0
votes
0answers
44 views

Updating table with GeometryFromText of PostGIS

I'm trying to update a column public_geometry in a table by creating a point from longitude and latitude cols from the same table. The database is PostgreSQL with Pgadmin as program to handle the ...
1
vote
1answer
32 views

Aggregating the attributes from nearby grids and assigning it to the parent grid in a fishnet

I have 700,000 grids generated to cover an administrative area. I want to calculate the sum of an attribute in its neighboring grids till 2nd order and assign it to each grid. To do so I have ...
0
votes
2answers
35 views

How you get the square containing a multipolyline

I use ST_Union to group a few lines. But realize some lines aren't supposed to be joined together after all. I would like to calculate the extent of the multipolyline so if the area is too big ...
0
votes
1answer
109 views

How install postgis_raster extention on PostgreSQL 11.5?

I want install postgis_raster extension on PostreSQL 11.5 + PostGIS 2.5.3 + MacOS 10.14.6 There is a install script on https://postgis.net/install/. All installations of extensions work fine, except ...
0
votes
0answers
18 views

Image Mosaic JDBC plugin for multiple tiles

I want to publish a large raster (10000 km², 0.5m resolution). My original data is several tiles of 5km². I've merge all tiles and published an unique raster file with Image Mosaic JDBC Plugin. But ...
1
vote
1answer
59 views

How do you 'incrementally' create vector tiles?

I am serving my own vector tile layer. In my current setup when a point of interest is added by a user it is stored in PostGis and the relevant zoomtiles are reproduced and written over the previous ...
1
vote
0answers
38 views

Invalid SRID in PostGIS when importing data by ogr2ogr

I am using PostgreSQL 12 with PostGIS 3.0.0 and GDAL 3.0.0 to copy data in it. The command I am using is: ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 dbname=db user=user port=5432 password=1" "file....
1
vote
0answers
23 views

Doing PostGIS query with Arel.spatial

I'm trying to understand how Arel work. How can I do this PostGIS query with Arel.spatial ? SELECT ST_asText(ST_ClosestPoint(track.path::geometry, pta.lonlat::geometry)) AS closet_point ...
1
vote
1answer
63 views

Return all results within a 2km,5 and 20km radius of a specific lat/long point?

I am using the below query for calculation but I don't know how to pass the radius first time I need 2 km and in second query need 5 km and so on: SELECT * FROM pwd_roads AS r WHERE ...
2
votes
1answer
59 views

QGIS Labelling misplaced

I'm developing a QGIS plugin along with PostGIS and I'm facing some problems with labeling. This plugin creates several layers and tables. Some of these layers are views (labeling layers), so if I ...
1
vote
0answers
59 views

Import SHPs and DBFs to PostgreSQL in pgAdmin 4 on a Mac

I am receiving the following error when trying to import a dbf file into PostgreSQL (v 12, pgAdmin 4) using the Boundless/OpenGeo Suite for Mac pgShapeLoader GUI (which was recommended for Mac users ...
1
vote
0answers
19 views

Get the measure M along a CircularStringM in PostGIS without first converting to a LineStringM

I've been struggling to get PostGIS 2.5 to run any linear referencing functions on geometry containing curves (CircularStringM, CompoundCurveM for example). Is it possible to get the measure M along ...
2
votes
0answers
43 views

How can I optimize this PostGIS query?

I am using this query in PostGIS to get the closests points around a trace. This query is taking a long time to run : 15 sec with only 800 records in the POIs table. The first query get a list of all ...
1
vote
1answer
62 views

Query shortest path from coordinates with pgrouting

How do I calculate the path, pass coordinates and, if possible, in the return function, have the route coordinate list? (I'm trying to plot the route with the ipyleaflet library in a jupyter notebook, ...
1
vote
0answers
49 views

Creating table of point in polygon results using PostGIS

I have one PostGIS table of polygons like this: index | lat | lon | geom where geom is a polygon d-type ex. POLYGON ((x1,y1,x2,y2.....)) CREATE TABLE buildings2 ( index integer PRIMARY KEY, ...
1
vote
1answer
34 views

Phanton lines in a PostGIS ST_Union

I created a polygon using ST_UNION. However, it shows some lines inside that don't make sense, as you can see below. How can I create the union poly without those lines or remove it from the poly ...
1
vote
0answers
37 views

PostGIS buffer in meters without geography

I am building an application where I need to build a radius n meters around a given lat, lon. the st_buffer overload that uses a geography type for the first parameter would work fine, however, I am ...
1
vote
0answers
48 views

raster2pgsql not in C:\Program Files\PostgreSQL\12\bin

After adding both C:\Program Files\PostgreSQL\12\bin C:\Program Files\PostgreSQL\12\lib to my windows environmental variables PATH thing, I can't access raster2pgsql: 'raster2pgsql' is not ...
2
votes
1answer
65 views

How can I get location data (country,city, etc.) by giving latitude and longtitude using PostGIS?

Currently I'm using the following query : SELECT ST_SetSRID(ST_MakePoint(x, y),4326); and it returns me location in numbers something like : 010100000000000000008012300000000000001230 but I wanted ...
3
votes
1answer
111 views

mapnik offline maps using generate_tiles_multiprocess.py

I've been working for a couple of days to get offline map tiles working for use in JXMapviewer2 but I am having trouble when using generate_tiles_multiprocess.py to process the tiles. My intention is ...
2
votes
1answer
65 views

Why do two tables with valid geometry return self-intersection errors when intersected?

I have two tables A and B, both have valid geometries when I check with ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error: ERROR: ...
2
votes
1answer
117 views

Batch importing raster TIFFs into PostgreSQL using Python

I have a fairly simple script where I aim to insert roughly 30 raster TIFFs into a PostgreSQL database. To manually import one TIFF at a time I run this command raster2pgsql.exe -s 2263 -d -C -I -M ...
2
votes
1answer
82 views

PostGIS raster calculate average slope of 3x3 neighborhood around a geometry point

I have two tables corners_final (vector point data) dem_slope (1ft slope DEM, calculated in degrees) With this simple query I am able to extract the slope value that intersects a given point SELECT ...
0
votes
1answer
18 views

How to set schema on GeoServer ImageMosaic JDBC?

I've publish raster from postgis to geoserver with ImageMosaic JDBC like explained on this post and it work properly on the default 'public' schema. But how to set a custom schema ?
6
votes
4answers
313 views

Varying size buffer along line with PostGIS

I want to create a buffer along a line where the buffer width corresponds to a fraction of the length of the line, something like that: To do that my idea is to compute for each node of the line (...
1
vote
0answers
45 views

Carto trying to set syncing with a PostGIS database connection

I'm trying to set up a syncing connection between Carto and a PostGIS Table. However, I'm not seeing updates in Carto, and the table does not show up in my list of syncs when I make a Get request. ...
-2
votes
1answer
63 views

Converting geom polygon to point x point y using PostGIS

I have data geom polygon like this: ...
1
vote
0answers
24 views

Obscure import GeoJSON to PostGIS by ogr2ogr

I import UTF-8 encoded GeoJSON files and get a different column value in PostgreSQL. Execution command one template is: ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 dbname=db user=user port=5432 ...
2
votes
2answers
45 views

Running ogr2ogr in Python on Windows

I run ogr2ogr to transfer geometrical data from a pgsql (PostGIS) database to a MS SQL one. It works quite well. I'm trying to achieve exactly the same result in Python, as I'm planning to use ...
1
vote
1answer
37 views

Measuring/4D relation querying within linestring using PostGIS

let's assume a Linestring(Z)M holding a timestamp as measure variable with each point. Is there a way to easily query "on the relation of those timestamps between the points". For example to find ...
1
vote
0answers
23 views

pg_ksp is extremely memory intensive for k>1

I am using pg_ksp (from the pg_routing extension for postgis) on a subset of the OpenStreetMap database with 400.000 nodes, if I set k=1 to find the best path the query takes about a second and around ...
1
vote
0answers
135 views

Selecting geometry from PostGIS compatible with Open3D

I have a cube stored as geometry in a PostGIS DB. I get the coordinates using the following SQL: select ST_AsEWKT(geom) from polyhedral.vol_1 where id = 1 This presents the following result. ...
4
votes
1answer
159 views

QGIS incredibly slow when trying to update large tables?

My point postgresql table has about 500k rows and about 20 columns People have about 5 fields that they update, the other fields are static Selecting the points is fairly instant, however using the ...
3
votes
0answers
213 views

How to setup QGIS Server with PostGIS?

I need setup QGIS Server + PostGIS (+ Lizmap) in Docker for our GIS users. I spent a lot of time reading documentation and searching for information on that matter but it's unclear for me as for ...
0
votes
0answers
14 views

Creating connection from GeoServer to PostGIS raster coverage? [duplicate]

I know how to connect GeoServer to PostGIS for visualization of vector data. However, I could not connect GeoServer to PostGIS for visualization of raster tables of PostGIS. If it is possible to ...
1
vote
0answers
45 views

High cost of geometry bitmap heap scan

I have a PostGIS database with tables osm_admin (with contains all administrative boundaries from osm data) and osm_landusages (with contains all landuse areas form osm - like forests, industrial/...
1
vote
1answer
41 views

SDO_JOIN conversion to PostGIS

I make refactoring from Oracle to PostGIS and I found out that PostGIS doesn't support sdo_join. How should I correct the statement below? SELECT e.ID AS EDGE1_ID, u.NODE_ID AS ...
0
votes
0answers
89 views

Unable to load PostGIS-table for QGIS on MacOS Catalina

I just updated to the QGIS 3.10 release under MacOS Catalina. In 3.8 I had no problem using PostGIS-data. When I now try to load the same PostGIS-layer in 3.10 it fails. When testing the connection, ...
2
votes
1answer
57 views

ogr2ogr: 'ERROR 1: Wrong field type for ID' with primary key

I have a shapefile with the following structure: I'm trying using ogr2ogr to import the above shapefile inside my PostGIS database with the following code: ogr2ogr -append -f "PostgreSQL" PG:"host=...
2
votes
1answer
67 views

Calculating distance between every entry in Table A and nearest record in Table B using PostGIS

I am new to PostGIS. How do I calculate the distance and direction between every site (approx 30,000 record) and the nearest entity in a gazetteer (approx 300,000 records)? Ideally a separate table ...
2
votes
1answer
36 views

Determining types of geometry in a single table?

I need to determine whether any tables within my database have more than one geometry type. For example, I want to know if there are Points AND multipoints within the same table. Currently, I have ...
2
votes
0answers
34 views

How to exclude the area of ​intersecting polygons from the total sample?

machine translation I am interested in solving such a problem. There is a table id | coordsPolygon 1 | 0103000020E6100 ... 2 | 01030000301F300 ... ... I have to make a query that returns the ...
3
votes
2answers
118 views

Calculating azimuth values relatively along line for its segments using PostGIS

I'm trying to make this calculation in SQL with PostGIS: azimuth_1 + (Sum rel_azimuth_2 to rel_azimuth_7) rel_azimuth_x is always the angle beetween the actual subsegment_x and one subsegment_x-1 ...
1
vote
1answer
63 views

PostGIS create table with multiple geometry

I want to create a table with a geometry field in it. e.g.: CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) ); In my case, this geometry field can either be a POINT, a ...
1
vote
0answers
19 views

PostGIS - find vertices on polygons without partners

My goal is to ensure that a set of boundaries have vertices that are co-incident where they intersect by a tolerance of 5m. So I am looking for orphan vertices that are not on an external boundary. I ...
2
votes
1answer
73 views

Calculating azimuth values along line for its segments using PostGIS [closed]

How can I calculate the azimuths along a line on its segments ? Is there a function in PostGIS for this? I want to calculate the min, max, sum and avg value of the azimuths for each segment.

1 3 4 5 6 7 146