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
1 answer
1k views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...
1 vote
0 answers
7 views

How to map a single PostGIS function to multiple Spatialite functions with GeoAlchemy2

In PostGIS we have ST_Affine(geom, a, b, d, e, xoff, yoff) but in Spatialite this functionality is split across 2 functions: ATM_Transform( geom , affine_matrix ), and ATM_Create(a, b, d, e, xoff, ...
0 votes
0 answers
16 views

Storage needed on a VM learning GeoServer

I want to learn GeoServer. How much storage space should I allocate when I create the VM? I'll be using Ubuntu 20.02 server and following the instructions here This guide doesn't give details about ...
2 votes
1 answer
18 views

Showing Origins on ARRAY multiple routes - pgRouting

I have routed between multiple points using pgRouting. I have aggregated the total cost of each route (length) into a new table using edge = -1 to be imported into an inverse distance weighting ...
2 votes
0 answers
30 views

pgRouting computation time & code efficiency

I am trying to find the closest landfill by distance from various locations, however, the computation time is 8 hr 36 min to find the shortest path from 3602 locations to 5 landfill locations. Before ...
0 votes
1 answer
313 views

Installing PostGIS extension with pgAdmin 4 on MacOS Monterey

I'm currently running into a problem on pgAdmin 4 where I am unable to use the PostGIS extension. I first tried running it this way through PGAdmin 4: DROP EXTENSION IF EXISTS postgis CASCADE; CREATE ...
3 votes
1 answer
43 views

Grouping polygons by similarity keeping records of ids

I have a layer made with overlapping polygons, some areas has the same feature overlap 100 times. What I want is to aggregate all the ids for those features which I have determined they have ...
-1 votes
0 answers
53 views

How import list of polygons to PostGIS table

I have a dictionary "polygon": [[168.4456025, 54.51134820], [168.4458053, 54.50852270], [168.445539, 54.48874220], [168.4419135, 54.4690740], [168.4254386, 54.57088510], [168.4356331, 54....
1 vote
0 answers
142 views

Stepping into C++ functions from external Python app while debugging [closed]

I just wanted to see how to step into the C++ class QgsVectorLayer()'s methods from the python call to QgsVectorLayer(self.uri.uri(),'foo','postgres'). I'm trying to debug and it trying to step into ...
3 votes
1 answer
57 views

Want to use PostgreSQL db and ST_AsMVT() to develop a tile server, assuming my start point is a GeoTIFF

So basically I want to use ST_AsMVT() function to generate a web server that generates vectors tiles for me, which can then can be accessed through a link like "http://localhost:8080/{z}/{x}/{y}....
2 votes
0 answers
34 views

Creating a grid which takes coordinates as an input

I need to create a grid, based on parameters. Dimensions of the grid (10 km x 10 km) Upper-left coordinate, which defines the start of the grid (upper-left corner) I was reviewing the following ...
1 vote
0 answers
36 views

Load shapefile from local machine to database PostgreSQL

I have a method that imports shapefile to PostgreSQL (using GeoTools), it takes as arguments the path of the shapefile (it's working fine). The problem is I want the user to select a shapefile from ...
5 votes
4 answers
2k views

"libSFCGAL.so.1: undefined symbol" upgrading to Postgis 2.2.2 (and 2.2.1)

I recently got PostGIS upgraded to version 2.2.2. I would like to upgrade a legacy database to this new version, running this query: alter extension postgis update to "2.2.2" The result is this ...
2 votes
1 answer
312 views

Problem to convert geometry to coordinates of geometry(MultiLineString) data

I am stuck converting a geometry(MultiLineString) into coordinates. The following I did. I download the data from this Austin Contours data link I import the shape files using PGAdmin III and ...
3 votes
2 answers
359 views

Creating precise shapes using list of coordinates?

I am using st_concavehull to create boundaries/shapes. This works, however, the shape is not precisely tracing the coordinates. It is more puffy, as if trying to even out or "clean up" the actual ...
5 votes
4 answers
114 views

Buffer with PostGIS in the form of a pentagon or hexagon

In PostGIS, the ST_Buffer function allows a user to specify the segements per quadrant. However, only polygons with multiples of 4 (4, 8, 12, 16, etc) are allowed. How would you buffer a point to a ...
11 votes
4 answers
459 views

Deleting small holes in polygons with PostGIS

I have a big MultiPolygon layer in which I want to delete holes smaller than 100m². QGIS do it well and fast with the algorithm "native:deleteholes", but I need to use it as part of a SQL ...
0 votes
0 answers
16 views

Create a grid of classification in postgre [closed]

I have a table that contains labels in vector type and I would like to create a grid of labels based on the input parameters of the grid. So parameters: grid dimensions, for example, 1 km x 1km upper-...
4 votes
2 answers
837 views

intersection in postgis takes hours and hours to run, why?

I have a table with approximately 500,000 records that I am trying to intersect with a table that has about 30,000 records. It was running for 11 hours until I finally stopped it. Why would this ...
0 votes
1 answer
101 views

Creating raster with cell value counts from another raster in buffer around each cell using PostGIS

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 ...
2 votes
0 answers
40 views

Finding and removing overlaps in a shapefile using PostgreSQL

I was wondering if it's possible to remove any overlapping areas that occurs in a column based on a sample of the table for the shapefile with 2 important columns: Shape area Geom I'm trying to find ...
2 votes
1 answer
78 views

How do I append a shapefile to PostGIS table with multiple geometry fields using ogr2ogr?

I am having an issue when trying to append shapefile data to an existing table in my PostGIS database using ogr2ogr. The existing table has multiple geometry fields. When I append the shapefile the ...
2 votes
1 answer
562 views

ERROR: function st_straightskeleton(geometry) does not exist

I'm trying to calculate center line from polygon, so i'm trying to do with postgis function st_straightskeleton but it is rising the error of function not found. postgis version: POSTGIS="2.4.3 ...
10 votes
1 answer
1k views

Selecting the longest connected lines from PostGIS ST_ApproximateMedialAxis

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
0 votes
0 answers
299 views

PostGIS SQL error in Java: no spatial operator found for st_contains

I'm quite new in PostGIS, and I'm trying to run an SQL query. I use this query to obtain a subset of segments from table roads in a group of polygons, then put the result in a separate table. There ...
2 votes
2 answers
485 views

Split lines by polygons PostGIS

I have lines that I would like to split at polygon boundaries. The line table is MULTI and the polygon table is MULTI and was derived from a MULTILINESTRING table that was buffered then unioned to ...
0 votes
1 answer
65 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
2 votes
1 answer
104 views

Is it possible to edit a non PostGIS layer within Lizmap?

I have a running LizMap + Qgis Server configuration (but I am only the frontend admin, another dude is in charge of the backend server therefore I can't access to logs easely). Our set-up is aimed to ...
2 votes
1 answer
29 views

st_dump - why is the "path" an array?

Looking at the postgis documentation, I read: For an atomic geometry type (POINT,LINESTRING,POLYGON) a single record is returned with an empty path array and the input geometry as geom. For a ...
1 vote
0 answers
26 views

Coverage concept and PostGIS [closed]

I'm trying to understand the implementation of the interplay of raster and vector data in PostGIS. In this article Coverage Concepts and PostGIS on the PostGIS wiki page the concept of vector and ...
2 votes
1 answer
21 views

Why is h3.polyfill(geojson, 15) returning empty list for this polygon?

I have a polygon the size of an office block in WGS84 coordinate system. When I run h3.polyfill at resolution 15 it should return a large number of h3 res15 addresses whose centroids are contained in ...
0 votes
0 answers
12 views

Qualified KNN query on SpatiaLite

Is there a way to do a qualified KNN query on SpatiaLite? I want to do a KNN search but specify some criteria on the table. For example, if I had a table of cities I want to return the K cities ...
1 vote
1 answer
52 views

Getting base Line of PostGIS skeleton

I'm interested in using OGR/GDAL, PostGIS, or QGIS in order to achieve the following: Working from the PostGIS docs, how would one further simplify this line to the middle line? I've tried utilizing ...
2 votes
0 answers
30 views

Generating cluster counts for map rendering using PostGIS

Each row in my table (1m+ rows and growing) has a field location containing the lat/lng Point coordinate. These points are rendered in a map. At higher zoom levels I am using Supercluster to cluster ...
0 votes
0 answers
25 views

How to set up a proper id sequence in PostgreSQL [closed]

I imported several layer with QGIS via batchprocessing to my PostgreSQL/PostGIS database. The problem was, that no sequence for the IDs was set up through this process. So I added sequences for my ...
3 votes
1 answer
112 views

In PostGIS, how to identify intersecting line segments, but not matching start and end nodes

I have a PostGIS table of thousands of linestring segments wherein each line segment has only two nodes, start and end. All of the lines have a start and/or end node that matches another line segment ...
3 votes
1 answer
65 views

How to make column in view to have correct srid of origin table

I have the following view CREATE OR REPLACE VIEW myview AS SELECT a.id, st_setsrid((st_dump(a.geom)).geom, 25832) AS geom FROM mytable a; on a table mytable with a geom column of the following ...
3 votes
1 answer
41 views

Get geohash neighbors with PostGIS

I’d like to get the 8 neighbors of the geohash grid. For example, the neighbors of the hashcode dqcjqc are dqcjqf,dqcjqb,dqcjr1,dqcjq9,dqcjqd,dqcjr4,dqcjr0,dqcjq8. I wonder how to do it in PostGIS? ...
5 votes
2 answers
47 views

Adding a polygon to a multipolygon column

I have a table that contains a multipolygon column. In my application, I use a view on the table which splits the multipolygon in several polygons: CREATE VIEW myview AS SELECT a.id, ST_SetSRID((...
5 votes
1 answer
239 views

T-rex tile server with PostGIS (Windows) : utf-8 encoding error

I want to use T-rex as vector tileserver on Windows. I load osm.pbf file (from geofabrik) to PostGIS database with osm2pgsql successfully. When I run the server with the following commands, I get the ...
3 votes
1 answer
36 views

Aggregating MultiPoints to MultiPoint output using ST_Collect

I have a geometry column that stores MultiPoints. How can I aggregate MultiPoints having another MultiPoint as a result, not a GeometryCollection? select st_collect(geom), id from my_table group by id ...
2 votes
1 answer
163 views

Scripted generating then loading rasters to PostGIS - raster2pgsql phantom

(Ubuntu server 18) It seems that raster2pgsql is included in the postgis package but I can't find it separately to download either source or binary. Supposedly there's an executable somewhere in the ...
1 vote
2 answers
142 views

PostGIS - Fast area computation on large overlapping (or not) polygon dataset

I'm co-developing an PostGIS based API that will work with complex and potentially large sets (>10k) of polygons, extracted from a large table following custom, user defined filters. I have ...
3 votes
0 answers
35 views

Area calculation for non-planar surfaces

I am looking for a way to calculate the area for a big set of 3D-surfaces. I know, that some of those surfaces are slightly non-planar, which means that the coordinates got somehow "rounded" ...
0 votes
0 answers
42 views

ora2pg SDO_Geometry to st_geometry

I am trying to migrate my database (Oracle) to postgres using ora2pg (v.23.1). Unfortunately the script converts our SDO_Geometry datatype into shape geometry(GEOMETRY,32632) instead of shape ...
2 votes
1 answer
68 views

How to get a list of lines, with a list of polygons that they intersect? [closed]

I am a new user in a PostGIS environment and don't even know where to start. I have two tables, one storing lines and the other storing polygons. I to create a query that can be used to get a separate ...
3 votes
1 answer
161 views

Grade separation routing using pgRouting

I am using pgRouting version 2.6. I am creating a routing topology from a subset of lion data https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-lion.page I have gone through the process of ...
0 votes
1 answer
67 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 ...
4 votes
1 answer
189 views

Unsplitting/merging lines that have coincident endpoints using PostGIS

I have a large dataset of lines/linestrings (ie. a road network), which is unregularly segmentised/ split and the lines needs to be merged at coincident endpoints (if only two lines are touching). I'd ...
4 votes
2 answers
2k views

Inserting Point into PostGIS geometry column from golang?

I have this go code to insert data in to postgresql: sqlStatement := ` INSERT INTO my_table ( column_name, geog) VALUES ($1, ST_GeomFromText('Point($2 $3)')) ` and then _, err = db....

1
2 3 4 5
173