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
6 votes
2 answers
53 views

Delete 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 ...
user avatar
  • 453
0 votes
1 answer
22 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 ...
user avatar
  • 1,049
1 vote
0 answers
16 views

Coverage concept and PostGIS

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 ...
user avatar
1 vote
1 answer
16 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 ...
user avatar
0 votes
0 answers
11 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 ...
user avatar
1 vote
0 answers
20 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 ...
user avatar
  • 11
1 vote
1 answer
42 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 ...
user avatar
  • 989
0 votes
0 answers
23 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 ...
user avatar
  • 1,903
2 votes
1 answer
61 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 ...
user avatar
  • 1,049
2 votes
1 answer
29 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? ...
user avatar
  • 247
3 votes
2 answers
39 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((...
user avatar
  • 1,049
2 votes
0 answers
23 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" ...
user avatar
  • 21
0 votes
0 answers
38 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 ...
user avatar
  • 2,494
2 votes
1 answer
63 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 ...
user avatar
3 votes
1 answer
106 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 ...
user avatar
  • 1,299
2 votes
0 answers
19 views

Interpolated for every point in the raster space

I have here made a demo of my attempt turn points on a map into a with values raster then use St_interpolateraster and than send it back as geojson. It returns values and they are interpolated well. ...
user avatar
  • 51
2 votes
0 answers
34 views

POINT and VALUE to Multipoint

I have a table of geom(POINT) and a value. How can I turn turning this into a Multipoint geometry so I can use it in interpolate? The end goal is to use this just using my Points and values. SELECT ...
user avatar
  • 51
0 votes
0 answers
22 views

PostGIS function ST_MakeEmptyRaster does not exist

First time using InterpolateRaster and it tells me Select ST_InterpolateRaster((ST_DumpPoints(st_collect(geog::geometry))).geom,'invdist:smoothing:2.0',ST_AddBand(ST_MakeEmptyRaster(200, 400, 10, 10, ...
user avatar
2 votes
0 answers
35 views

IDW inpterpolating PostGIS

I have a bunch of points with a temperature value in PostGIS stored as geom. How can I IDW interpolate between these and return a GeoJSON? I know how to return a GeoJSON from my points but I have no ...
user avatar
0 votes
0 answers
14 views

ST_AsRaster equivalent function in Python

I have a table in PostgreSQL such as the one below. fid name rating geometry osgb43210 Smith Street 1 POLYGON ((507300.52 428808.91, 507299.2 428814.15, 507298.48 428813.98, 507299.37 428808.68, ...
user avatar
  • 37
1 vote
1 answer
34 views

Can't import a shapefile with different SRID in a geometry column

PostgreSQL 13, PostGIS 3, QGIS 3.22 In a table, I had a geometry column geom with a SRID 2154. Now I have to store geometries with different SRIDs in geom. So I did : alter table activite.prescription ...
user avatar
  • 1,035
0 votes
0 answers
20 views

Mapbox layer doesn't update on variable change | react-map-gl

I have a map with a custom layer. Right now I have a problem that when points state get updated the map doesn't rerender. How can I force a rerender of the map layer when points changes? Codesandbox: ...
user avatar
  • 51
1 vote
1 answer
34 views

Use PostGIS to find all the most outer points

I am trying to use POSTGIS to find all the outer points when I have alot of points? How would I go about doing this and and I cant seem to find a command for it.
user avatar
0 votes
1 answer
26 views

Insert a waypoint into a POSTGIS table with psycopg2 and ST_GeographyFromText

I am trying to insert a waypoint into a table with 1 foreign key refererance and a geography point in PostGIS. I am having no luck. The table structure is CREATE TABLE route (id serial ...
user avatar
  • 11
0 votes
0 answers
24 views

How to import shapefile to PostgreSQL via PostGIS Shapefile Import/Export Manager?

When I after opened shapefile and click to button Import Log Window gives back error message: dbf file (.dbf) can not be opened. Shapefile import failed. I try to open the dbf file and still repeat ...
user avatar
1 vote
1 answer
28 views

Geometry Viewer in pgAdmin 4 displays a straight line instead of polygons of European union countries

Via PostGIS I import a shapefile layer with polygons of EU countries. I try select all rows and after still display blue straight line without the end. Where is the problem?
user avatar
0 votes
0 answers
12 views

QGIS: Exporting labeling information to PostGIS point layer

In order to comply with a given data model which is exported from a PostGIS schema, I need to export a set of label attributes (object id of labeled object, label text, label position, label ...
user avatar
  • 133
3 votes
0 answers
51 views

Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

As stated here, I am trying to load a virtual layer from two existing PostGIS tables, using a simple LEFT JOIN statement: SELECT t.id, t.geometry FROM table_t t LEFT JOIN table_f f ON f.fk_poly_id = t....
user avatar
  • 6,698
1 vote
0 answers
26 views

geog GEOGRAPHY(Point) insert creates syntax error [closed]

Why does this return syntax error at or near "5.1963". The documentation clearly states this is how to do it from my understanding? http://postgis.net/workshops/postgis-intro/geography.html#...
user avatar
  • 51
2 votes
1 answer
31 views

Importing single-geometry data to postgis

I have several single-geometry-layers (geopackages, shapefiles, temporary layers). Some are LineString, others are Polygon. When I use the DBManager in QGIS to import these layers to the PostGIS ...
user avatar
  • 1,903
1 vote
0 answers
35 views

PostGIS/PostgreSQL Heavy performance issues with SUM

Not sure if this is specific of GIS stuff or simply a SQL performance issue, but due to the nature of the issue and that I am working with H3 data types here it goes I have a query that runs very ...
user avatar
2 votes
1 answer
41 views

PostGIS function that updates WKT geometries to geometry type with st_geomfromtext [closed]

I come from the Python world but I need to create a PostgreSQLl function that providing a table will: Find columns that have "_wkt", i.e. "geom_wkt" or "geom2_wkt". Do ...
user avatar
0 votes
0 answers
29 views

Add/paste features to PostgreSQL table

I have a PostgreSQL database with several tables. One of them is a non-geometry table that is linked to other geometric tables. I am now trying to copy/paste data from a .csv file to this non-geometry ...
user avatar
  • 1
0 votes
1 answer
34 views

Calculating the distance For each Row with respect to a Common Point

I have two models with no relation Port and a WareHouse each with a lat and long fields. I want to be able to get the distance for each warehouse from the Port and maybe get the closest e.t.c. This is ...
user avatar
  • 101
1 vote
1 answer
24 views

raster2pgsql no more INSERT/COPY statement in the log

I use raster2pgsql every day with Windows command tools, and I'm searching a command to not prompt the INSERT or COPY statement on the terminal because I've got a lot, like an inverse verbose. Is it ...
user avatar
2 votes
0 answers
28 views

Multiple login challenges from PostgreSQL when using QGIS

I am using PostGIS as my main data storage. I have two different geodatabases, one with internal data and one with various background maps. Both those connections are set up with stored user ...
user avatar
  • 2,650
1 vote
1 answer
45 views

PostGIS: get Circle to GeoJSON

CREATE TABLE IF NOT EXISTS public.global_circles ( name character varying(64), geom circle); INSERT INTO global_circles (name, geom) VALUES ('My circle', CIRCLE(POINT(16.56115880637268,46....
user avatar
  • 85
0 votes
0 answers
26 views

Export 3D data from PostGIS to any common open and standardized 3D file format

I am mainly curious, because I found this function: ST_AsX3D() in the list of PostGIS 3.2 "ST_As{something}" functions: st_asbinary, st_asbinary, st_asbinary, st_asbinary, ...
user avatar
  • 6,698
2 votes
2 answers
72 views

undocumented PostGIS ST_Distance function taking text as parameters

It seems that I have a variant of ST_Distance function in my PostGIS (3.2.1) that is not on the documentation CREATE OR REPLACE FUNCTION public.st_distance(text, text) ...
user avatar
  • 10.3k
0 votes
1 answer
61 views

psycopg2 cursor.copy_expert() BadCopyFileFormat: extra data after last expected column when writing WKB data

I am trying to write a WKB representation (i.e. a binary string) of a Shapely Polygon into a PostgreSQL/PostGIS database using psycopg2 cursor.copy_expert() for a bulk insert. I narrowed down the ...
user avatar
  • 6,698
2 votes
0 answers
32 views

DB Manager shows a question mark in front of newly created PostGIS layer

I have a polygon layer in a PostGIS database with 16 polygons for the states of Germany. I want to create a new layer for the state of Saxonia in the east of Germany, which has also a different ...
user avatar
  • 1,903
3 votes
1 answer
42 views

Rounding decimal numbers in PostGIS [duplicate]

I have a 'cities' point layer. I want to compute a table with the coordinates of the geometries which works fine with that expression: select name, ST_X(geom) as x_coordinate, ST_Y(geom) ...
user avatar
  • 1,903
1 vote
0 answers
28 views

PostGIS role limitations overcome via QGIS

I'm using PostgreSQL 14 with PostGIS extension. I've created the following role and grants create role prueba login nocreatedb password 'prueba'; grant select, references, trigger on all tables in ...
user avatar
  • 2,271
2 votes
1 answer
46 views

Should I use different projections to calculate distance and displaying on the Map?

I'm using the UK crime dataset provided by https://data.police.uk/ My question is about coordinate systems/map projections. The crime locations are stored with the projection EPSG 4326 which uses ...
user avatar
0 votes
0 answers
23 views

Fix geometry to run pgr_createTopology which is very slow and fails

I'm trying to create topology in PostgreSQL using pgr_createTopology but it's very slow and fails after processing 4000 nodes. There are 300,000 nodes. I tried the same query with another road table (...
user avatar
6 votes
1 answer
149 views

Converting 1 meter to degrees at specific location using PostGIS

Given a point in EPSG:4326 I need to know how many degrees (approx) 1 meter is at this location using PostGIS. Feels like http://postgis.net/docs/manual-2.1/ST_Line_Interpolate_Point.html is useful ...
user avatar
  • 163
1 vote
0 answers
38 views

Storing a large 3D TIN into a PostGIS database

I have to store a massive TIN (1.5 GB) into a PostGIS database to do some computations with it. After my first try, I was getting a "server closed the connection unexpectedly" issue when ...
user avatar
  • 6,698
3 votes
1 answer
50 views

PostGIS performance using ST_Subdivide [closed]

What is the max or optimal number of vertices for a geometry to improve performance and load time, using ST_Subdivide? What would be the max or optimal shape length? I am trying to find the best ...
user avatar
2 votes
0 answers
39 views

PostGIS ST_Split outputs without a split

I have a PostGIS workflow that has a line file that has been buffered then subdivided and a vectorized raster called myvector. My intention is to split or separate myvector based on the bounds of the ...
user avatar
2 votes
0 answers
67 views

ogr2ogr errors: PROJ: proj_create no database context specified, cannot prase CRS, proj.db lacks database.layout.version.major/minor metadata etc

I have a PostgreSQL/PostGIS database that I have been working on and it's generated in a docker container. I access it via PGAdmin to the docker DB or sometimes I create a backup and restore locally ...
user avatar

1
2 3 4 5
173