Skip to main content

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
0 answers
30 views

Error when Saving PostgreSQL Cloned Table

I'm facing an issue while working with a cloned table from a PostgreSQL schema. When attempting to make changes like adding new vector layers or deleting a layer and then saving the project (from QGIS)...
user231934's user avatar
2 votes
1 answer
52 views

Separating mixture of two different coordinate systems in PostGIS table

One PostGIS table includes features (point, lines, polygons) with two different coordinate systems: EPSG:27700 (BGN) and EPSG:4325 (WGS84). Spatial extent of the table in WGS84: 52.6967,-3.1401 50....
foxhq's user avatar
  • 141
4 votes
0 answers
81 views

Spatial database design [closed]

I am designing a spatial database and I wanted to ask if it is a good idea to use inheritance to store objects that share certain attributes, but whose geometry differs since some are points, lines ...
Javier Aguilar's user avatar
1 vote
1 answer
89 views

Latitude/longitude from PostGIS table with coordinates transformation not working

I have a PostGIS (PostgreSQL 13/PostGIS 3.1.1 on Debian 11.7) table with points in EPSG:6708, so it's plain coordinates (data from Northern Italy). I need to get lat/long coordinates, so I do: ...
Luca Pauluzzi's user avatar
1 vote
0 answers
44 views

GeoServer layers data not showing on hosted WebGIS [closed]

I made a webgis and it has feature adding layer from GeoServer. It works fine on localhost but when I hosted the website its only hosting the HTML and cannot get/show the data from the GeoServer. Is ...
Maulana Heno's user avatar
1 vote
1 answer
86 views

Formation of MVT on the basis of GeometryCollection

The PostGIS documentation for the ST_AsMVT method states that ST_AsMVTGeom can accept a GeometryCollection for the initial formation of an MVT object. I execute ST_AsMVTGeom with ST_Collect combining ...
Roman's user avatar
  • 53
4 votes
1 answer
186 views

How SRID is represented as part of EWKB [closed]

I am trying to find the spec that defines how the SRID metadata is included as part of EWKB in PostGIS. Which bytes/hex values represent the SRID metadata in EWKB? Where can I find a spec on EWKB ...
Riyafa Abdul Hameed's user avatar
2 votes
1 answer
142 views

How do I share my data to others with PostGIS/QGIS

I only started to learn using PostgreSQL with QGIS. Since I wouldn't want my local machine to be actively running PostgreSQL in the background for always-on access, is there a way to "share" ...
Cookie's user avatar
  • 355
2 votes
1 answer
77 views

Difference between PostGIS pgrouting and Lizmap pgrouting

I'm starting to work with pgrouting. I know that pgrouting is an extension of PostGIS. I saw that Lizmap (which I currently work on) provides a module called pgrouting. Two points are drawn from the ...
Guido Fonzo's user avatar
1 vote
0 answers
74 views

Updating existing PostgreSQL database with OSM data

I currently work on the project which is directly working with the geo data of the country where I live. The database we have is quite huge, containing not only the geo data but also some other stuff. ...
qwerty's user avatar
  • 11
0 votes
0 answers
84 views

Uploading large OSM files to PostGIS with command line

I'm trying to upload the nationwide US OSM dataset from Geofabrik website to a PostGIS database (about 200 GB). For small OSM files, I'm able to import the OSM file as layers into QGIS with the ...
Vivek Reddy's user avatar
2 votes
1 answer
77 views

pgr_withPointsCost returns incorrect table values

I have been struggling with getting pgr_withPointsCost to output a correct cost table for months. I have created a routable network, ensured the network is connected, created a poi table with all the ...
MJM's user avatar
  • 975
1 vote
0 answers
50 views

Visualize a linestring and point from a PostGIS database in QGIS?

I ran a query in PostGIS table using a table of roads/linestrings in Hawaii and another table with 400,000 (longitude,latitude) points in Hawaii. The output table is meant to provide nearest road in ...
Vivek Reddy's user avatar
2 votes
1 answer
73 views

How to use ST_Union if polygons share the same name using PostGIS SQL

Say I have a polygons that overlaps with each other, How do I achieve the same output as dissolve -> dissolve field(check "name") using SQL code? layer fields information: - name # <-- ...
Cookie's user avatar
  • 355
2 votes
2 answers
95 views

How to prevent duplicates from being generated in Spatial Join in PostGIS

I'm trying to create a spatial view which meets the following criteria: Sentinel hotspots registered within 2km of powerline (feeder) AND QRFS point located within 4km radius of sentinel hotspots AND ...
Fezter's user avatar
  • 21.9k
0 votes
0 answers
42 views

Store remote sensing metadata created from STAC into PostgreSQL/PostGIS

I have created some STAC catalogs for remote sensing data (Landsat, etc.) that I would like to store in PostgreSQL/PostGIS for easier access and management. How would I import the STAC created ...
Clouseau's user avatar
0 votes
0 answers
55 views

Does ST_ClusterKMeans benefit from a spatial index?

Does ST_ClusterKMeans benefit from a spatial index? here is how I am using it (24313 is projection in meters): ST_ClusterKMeans( ST_Transform(d.bf_polygon, 24313), ...
analyst92's user avatar
  • 847
1 vote
1 answer
177 views

How to check if a raster in PostgreSQL table is tiled

I have inherited a PostgreSQL db and I am trying to run queries like this: UPDATE geomtable a SET column = ST_Value(b.rast, 1, a.geom) FROM rastertable b WHERE ST_Intersects(b.rast,a....
Goomer's user avatar
  • 13
2 votes
1 answer
112 views

Query to find nearest road highway tag to a (latitude,longitude) point

I imported an OSM file for the state of Delaware into the PostGIS Database via connecting to it through QGIS. I would like to build a query to retrieve the nearest roads to the (latitude,longitude) ...
Vivek Reddy's user avatar
2 votes
0 answers
152 views

PostGIS error during a simple SELECT statement: Geometry SRID (2056) does not match column SRID (21781)

I am facing a strange error in a PostgreSQL 13.6 + PostGIS 3.2.0 database on a Window 10 machine: mydb=# SELECT * FROM my_schema.my_view; ERROR: Geometry SRID (2056) does not match column SRID (21781)...
swiss_knight's user avatar
  • 10.4k
1 vote
0 answers
47 views

PostGIS selecting points using a subset of lines using ST_DWithin [closed]

I'm not sure why this script sends a much higher count than the real number. This one sends 2314 points, which the answer should be 114 I can get the correct value by either dissolving all the ...
sMartines's user avatar
  • 121
1 vote
1 answer
61 views

Minimum spanning tree (MST) over a set of POINT geometries

I have 3 columns in my table: centerPoint (point), uuid (string), and "areaID". Each areaID can have multiple uuid's, and each uuid has a corresponding centerPoint. I want to calculate the ...
analyst92's user avatar
  • 847
2 votes
0 answers
42 views

Recursive nearest neighbour to find shortest route

I am trying to find the shortest route using resursive nearest neighbours. Here is my code: with RECURSIVE NearestNeighbor AS ( SELECT a.uuid AS from_area, b.uuid AS to_area, ...
analyst92's user avatar
  • 847
2 votes
0 answers
84 views

PostGIS (hopefully faster) alternative to v.overlay with or condition from GRASS

I have two distinct tables, one called rpg(id, code_culture, geom --polygons) and the other called ia(id, classe, geom --also polygons) Like this : How should I proceed to get a single table with a ...
Geo7's user avatar
  • 21
1 vote
0 answers
41 views

Problem with GeoServer when I try to add data storage "Error message: Timer already cancelled"

I have a problem with GeoServer when I try to add data storage give me this: " Error message: Timer already cancelled Does someone have any idea of this problem and a solution?
Fabián Terán's user avatar
0 votes
1 answer
102 views

Represent linestrings with arrow direction based on origin and destination points location

I have two layers: one linestring layer with two attribute fields that identify the codes of the origin (field A) and of the destination (field B) nodes; one point layer with one attribute (unique ...
Michele Cordini's user avatar
1 vote
0 answers
88 views

Read only certain columns (read permissions) of a PostGIS database in QGIS

In our administration, each employee is given various rights to read and edit data on the PostgreSQL/PostGIS database. To view the geodata, we use QGIS. In this case there should be read permissions ...
cc_schr_gis's user avatar
1 vote
1 answer
51 views

PostGIS - Distinguish between geometry updates and value updates [closed]

I want to create a function that puts the current date in column 1 if any of the columns values are updated (except the geometry) and puts the current date in column 2 if the geometry is updated. Can ...
User's user avatar
  • 175
0 votes
0 answers
79 views

Hard upgrade of PostGIS - no postgis_restore in Ubuntu package

I'm running Ubuntu 22.04 with Postgresql 14 and PostGIS 3.4 installed from the repositories. I'm trying to follow the steps in the PostGIS docs for a hard upgrade of an old database dump. Step three ...
PeteBrew's user avatar
  • 133
3 votes
0 answers
74 views

Fixing gaps in line network with pgRouting/PostGIS

I have a line network that I want to fix the gaps on (connect the edges). I have tried the following: SELECT pgr_nodeNetwork('migration.streams', 0.0001, 'id', 'geom'); -- fix gaps and dead ends? ...
MJM's user avatar
  • 975
2 votes
1 answer
33 views

Expected type of geom is integer,smallint or bigint but USER-DEFINED was found

I have tried importing my multilinestring shapefile from both qgis and the shapefile loader into postgis, but it gives me the following error when running the following code: SELECT pgr_createTopology(...
MJM's user avatar
  • 975
-1 votes
1 answer
98 views

Reverse geocoding around the whole world using PostGIS [closed]

I need to determine an exact country and city, having geocoordinates on hand (the whole world), I've read tons of information on the internet, but wasn't able to find a solution. I was thinking about ...
Majesty's user avatar
  • 101
2 votes
0 answers
44 views

Calculate kinematic data within a PostGIS trajectory [closed]

I have a number of glider flight tracks recorded in GPX format and I would like to extract kinematic data such as instantaneous vertical velocity for charting and further analysis. There's plenty of ...
user19425's user avatar
  • 315
0 votes
1 answer
31 views

Create x1,y1,x2,y2 columns of pgRouting a* algorithm from a GeoJSON/shapefile

I have used pgRouting in PostgresSQL to routing using Dijkstra's algorithm. Now I need to do the same with a* algorithm, The A* algorithm needs x1,y1,x2,y2 values instead of geometry. How can I ...
Vignesh M's user avatar
1 vote
0 answers
176 views

"Shape Integrity error" on ArcMap when reading from PostGIS database [closed]

I have a query that creates Polygons/Multipolygons from union of a polygons from table in postgres using postgis. The spatial objects created all read as valid in postgis and all are Polygons/...
Sam's user avatar
  • 11
1 vote
0 answers
40 views

Discrepancy in Routing Results Between pgr_TSP Versions (3.1.2 vs. 3.2.1) [closed]

I've been working with the pgr_TSP function in the pgrouting library, and I noticed a change in its behavior between versions 3.1.2 and 3.2.1. Specifically, it seems that in 3.2.1, the algorithm was ...
postgis-user's user avatar
3 votes
1 answer
234 views

From Geodataframe to PostGIS

I have a geodataframe which I am trying to save to a postgis database. I have already created a connection to postgis in pgAdmin (username, password, localhost and table) from geoalchemy2 import ...
bravopapa's user avatar
  • 475
3 votes
1 answer
94 views

Find the median line from the input broken line

I already tried to use ST_Simplify, but as the result Wrong Line was gotten, because the function reduced the number of points significantly and curvature was lost. Is it possible to get the Desired ...
Slava's user avatar
  • 65
2 votes
1 answer
187 views

Is there a SpatiaLite equivalent to PostGIS' ST_DumpPoints? [duplicate]

I've been using PostGIS' ST_DumpPoints to 'explode' multipolygon layer into points with its corresponding polygon part, ring and index information. As QGIS uses a SpatiaLite engine against its Virtual ...
EranGeo's user avatar
  • 1,182
3 votes
1 answer
137 views

Find out which polygons that are inside a surrounding polygon

I have a table with a lot of polygons, some polygons are inside a surrounding polygon. I would like to set the table attribute "inside" to "1" if it's a polygon that are surrounded ...
Paul's user avatar
  • 477
0 votes
1 answer
117 views

Create multiple rasters in PostGIS from a (xyz) table

I'm having a hard time understanding how PostGIS works in terms of raster creation. Let's say I have a table with 14 columns: the first two are x and y coordinates followed by the air temperature for ...
Andrei Niță's user avatar
1 vote
1 answer
110 views

GeoPandas data types from PostGIS are always string

I am using GeoPandas from_postgis function to read data from PostGIS. However, the data types of the GeoPandas columns are always object. Data I store in PostGIS is mixture of text/char, bool, integer ...
foxhq's user avatar
  • 141
0 votes
0 answers
43 views

osm2pgsql : How to store "nd ref" data to a column in PostgreSQL/PostGIS

I'm using osm2pgql to import an OSM map. I imported it successfully but I need also the information in the "nd ref" node of the OSM XML. I.e. my OSM XML has a node like: <way id='1805' ...
Emanuele Gian's user avatar
0 votes
0 answers
58 views

Import time when converting NetCDF to PostGIS

I have a series of NetCDF file (from Copernicus, and some calculation) and the info from gdalinfo are: gdalinfo nc/2021-12-01_preprocessed.nc Driver: netCDF/Network Common Data Format Files: nc/...
lsir's user avatar
  • 1
1 vote
1 answer
55 views

Custom PostGIS function not visible in pg_tileserv layer list

I have created a new PostgreSQL function whose code is below CREATE OR REPLACE FUNCTION public."my_test"( layerid integer, z integer, x integer, y integer) RETURNS bytea ...
Asad Abbas's user avatar
  • 1,634
0 votes
0 answers
96 views

Debugging import of cloud optimized GeoTIFFs into PostGIS

I have a fairly basic setup where an Apache web server is sitting alongside a PostGIS instance, all run and created together using docker compose. I have a number of cloud optimized GeoTIFFs (COGs) ...
rumski20's user avatar
  • 588
3 votes
1 answer
144 views

Reading GeoTIFF from path and converting it into PostGIS raster data type

I need to read the raster file (DEM) from path directory and turn it into PostGIS raster data type. I formed this SQL query: SELECT ST_FromGDALRaster(pg_read_binary_file('D:\dems\img.tif')) FROM ...
Anna's user avatar
  • 43
3 votes
1 answer
130 views

Date management problem in a trigger with PostgreSQL [closed]

I created two tables on PostgreSQL. I would like to note in a layer a date and retrieve: the corresponding year in a variable, then reuse the year variable in various other attributes integrating ...
fcka's user avatar
  • 923
2 votes
0 answers
99 views

Use psycopg in pass parameter of type geography(MultiPolygon, 4326)[] to PostgreSQL function

I am using PostgreSQL + PostGIS to save a country border. Following is the table definition: CREATE TABLE IF NOT EXISTS country ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(...
timewalker's user avatar
2 votes
1 answer
102 views

Calculate road distance between two points in PostGIS - Resultant distance is not accurate (not even close to the actual distance) [closed]

SELECT torigin1.id AS origin_id, tdest1.id AS destination_id, CASE WHEN ST_LineLocatePoint(troad.line_string, torigin1.geom) < ST_LineLocatePoint(troad.line_string, tdest1.geom) THEN ...
mangotree's user avatar

1
3 4
5
6 7
189