Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

Find nearest polygons of a multi-line string

I am trying to find the nearerst (100-200 meters) parcels of the green line. Given The photo below, they database should return 12 records. I am using ST_DWithin postgis function. Is this the correct ...
2
votes
1answer
27 views

PgRouting : Shortest path starting from the closest vertex not the closest node

I am currently working on a routing solution with PgRouting (Dijkstra's algorithm), but the shortest path it creates always start from the nearest nodes generated from the edge table, and sometimes ...
0
votes
1answer
16 views

TypeError: Invalid parameters given for Point initialization.on Django

[![server error][1]][1]I am working on a location based poject and what I all want is to show only those pointfields stored in database which are at certain distance from any clicked region of ...
1
vote
0answers
49 views

Ensuring all points are a certain distance from polygon boundary

I have some PostGIS SQL that generates uniformly distributed points within polygons based on area (roughly following this tutorial). These points represent field sampling locations. Some of the points ...
0
votes
0answers
13 views

Using PostgreSQL to calculate ST_Volume on XYZ data

I have a set of XYZ data which I want to query in PostgreSQL in order to find the volume. I've been trying the ST_Volume on the geometries (have also used ST_MakeSolid and ST_Tesselate) but all ...
4
votes
1answer
134 views

Create a PL/PSQL function that performs as fast as a normal query?

I'm trying to create a PL/psql function that cuts one user-defined geometry to a predefined border geometry. If the user-defined geometry does not intersect with the border geometry, it should return ...
0
votes
1answer
24 views

Error loading GeoTIFF using raster2pgsql: “Error 1: PROJ: Cannot find proj.db”

I am attempting to load a GeoTiff into a PostgreSQL database using the PostGIS extension raster2pgsql. When running the executable, I receive the following error: Error 1: PROJ: Cannot find proj.db. ...
0
votes
0answers
29 views

Error connecting to a PostgreSQL database from QGIS using SSL certificates

I'm trying to connect QGIS (v3.14) to a PostgreSQL database hosted on Google Cloud. I've successfully connected to the database through pgAdmin (v4.23) so my assumption is that there isn't a problem ...
1
vote
2answers
33 views

PostGIS: Syntax for ST_Collect into a multipolygon keeping internal boundries

I have a PostGIS table with many polygons representing islands these all form a common group How do I create a new MultiPolygon (keeping internal boundaries) and give it a new name? CREATE TABLE ...
3
votes
1answer
42 views

PostGIS check if multiline string is included in polygon

I want to return all records that the multiline-string is included in the polygon. In the photo below, it should return two records. Green line (streets) is contained inside two neighbourhoods (white ...
0
votes
1answer
38 views

Error appending data to existing PostgreSQL database for OSM with osm2pgsql

When running osm2psql on an existing database I get an error osm2pgsql -v -d gis -a -s -G --flat-nodes ./nodes.cache --number-processes 8 ./thailand.osm.pbf Output is : Allocating memory for dense ...
3
votes
3answers
85 views

Missing geometry type and SRID in PostGIS

I created a view with select a, st_union(geom) from table group by a srid: 25832, geometry type: Multipolygon The view has no SRID and the geometry type is "GEOMETRY". Now i want to ...
1
vote
0answers
31 views

Error 0x8004159D while loading data from PostGIS to ArcGIS Pro

I successfully connected to the PostgreSQL database, and I can see the tables/data. When I load one of the data (linestring data) it works well. But when I loaded the polygon data it prompted the ...
1
vote
1answer
29 views

How do you store/organize/manage your own data? [closed]

Lately I was wondering how one could or should store his/her own data. As I studied GIS and I am working on different private projects using GIS data, a lot of data have accumulated on my local hard ...
0
votes
1answer
41 views

Google Geocoding API to geometry (Multi-line string)

I am using the Google Geocoding API. I need to use that response and convert that to geometry. How is this possible? What postgis function should I use and what parameters to assign? Raw JSON data: &...
3
votes
1answer
45 views

Calling PostgreSQL procedures or functions from QGIS' Query Builder

I am trying to call either a stored procedure or function defined in the PostgreSQL/PostGIS database and use it from the QGIS Query Builder. Neither work: the function returns a table that cannot be ...
0
votes
0answers
8 views

pg_dump on tiger.loader_platform not having any inserts

I am trying to backup the tiger.loader_platform" table. There are three rows in the table. I have tried this command on the same same database under the public schema and I am getting insert ...
0
votes
1answer
30 views

Upgrading the PostgreSQL within QGIS

Due to a vulnerability being flagged by Fortinet, I'm looking at upgrading the version of PostgreSQL that's used in our QGIS install. Is it possible to upgrade this without breaking QGIS, and how ...
1
vote
1answer
24 views

Zoomable world map with small countries highlighted

I'd like to create a zoomable world map where countries/territories are color-coded. This is a pretty standard request and Carto has a nice example. However, in standard maps like this, small ...
4
votes
1answer
70 views

Cluster points in each polygon into n parts

I am working on adding 2000 random points to each of 2 polygons and then clustering the points in those polygons into 4 clusters for each polygon. When I apply ST_ClusterKMeans() in PostGIS, it ...
0
votes
0answers
27 views

Query geodatabase with PostgreSQL to show - schema, table, column, domain name

How to query geodatabase system tables using PostgreSQL to result this: schema, table, column, domain name I found this, but nothing for PostgreSQL https://desktop.arcgis.com/en/arcmap/10.3/manage-...
0
votes
0answers
63 views

Problem with massive data importation to PostGIS/PostgreSQL

I need to import a huge SIG data into PostgreSQL (my shapefile table have almost 1.700.000 rowns and 43 columns). I'm using PostgreSQL 12.4 and PostGIS 3.0.x, that came together with PostGIS Bundle 3 ...
1
vote
1answer
26 views

PostGIS combine ST_DistanceSphere with Nearest Neighbor search

I have two tables both have point geometry with SRID 4326 (WGS84) I need to find the nearest point in 1 table to every point in another table PostGIS KNN search is written t1.geometry <-> t2....
3
votes
3answers
58 views

Calculating value of unique points in polygon in PostGIS

I'm working with a spatial PostgreSQL database that contains species records, there is a point layer 'spp_points', each point has a "spp_ID" and "spp_score" field. I also have a ...
2
votes
0answers
32 views

QGIS Server, Postgres (PostGIS) data, and password less data access with pg_service.file on Windows

We are trying to put set QGIS Server on Windows via IIS. The project on the QGIS Desktop uses pg_service.conf file without problem. When the project is moved to the Windows server, we set the ...
1
vote
1answer
44 views

shp2pgsql: not found on Debian 10 PostgreSQL 13

I have installed PostgreSQL 13 by using the following repository deb http://apt.postgresql.org/pub/repos/apt buster-pgdg main and I have installed PostGIS using apt install postgresql-13-postgis-3 ...
0
votes
1answer
33 views

Execute loop in PostgreSQL query

How to execute for each table in the list one fixed query replacing just %table% in PostgreSQL (like variables and loops) sql = 'select * from $table$ where...some_filter' $table = {tb1, tb2, tb3...}...
1
vote
0answers
23 views

Can QGIS load a georeferenced (skewed) raster from PostGIS database?

Summary I can't load a georeferenced/skewed raster in QGIS if it is stored in a PostGIS database. Problem Details For some reasons, I need to create a skewed raster in a PostGIS database, and use it ...
3
votes
2answers
181 views

PostGIS functions for generating centerline from polygon

I want to create a centerline from a polygon and I have tried using both ST_StraightSkeleton and ST_ApproximateMedialAxis functions in PostGIS. It gives an error with a message straight skeleton of ...
1
vote
1answer
25 views

Use Common Table Expressions with pgr_TSP

I have a query that uses a handful of CTEs to return a generated set of points. The actual query is more complicated, but the following will do as an example: WITH geom AS ( SELECT unnest('{ ...
1
vote
0answers
18 views

How to verify that all dependency libraries for PostGIS are installed correctly and working correctly? [closed]

I am attempting yet again to build PostGIS from the source to enable the fabled address-standardizer and the reverse-geocoder plugins for PostgreSQL. I have carefully discovered the source ...
1
vote
1answer
26 views

How can I change the “NoData” value for a raster column in PostGIS?

I have a raster column with "NoData" value of 3.40282346638529e+038 in PostGIS. I want to change the NoData value to 100. I used the following code: SELECT ST_Reclass(ukc,1,'3....
0
votes
1answer
25 views

Update table view Error in QGIS

When trying to make changes to a PostgreSQL view I get the following errors. Can someone advise how to get this working? I'm not familiar with triggers.
1
vote
2answers
45 views

“ERROR: operator does not exist: geometry public.&& geometry” after changing PostGIS extension schema

I just moved PostGIS extension (I have version 2.5) from public schema to another (mob) schema by following the docs at this official PostGIS page. I did: ALTER DATABASE mob_odm SET search_path = ...
1
vote
1answer
28 views

Inserting GeoJSON in PostgreSQL using laravel controller

Actually.. I have GeoJSON that I send from javascript into controller. My GeoJSON: { "type": "FeatureCollection", "features": [{ "type": "...
2
votes
1answer
38 views

PostGIS slow ST_Intersection

I'm trying get get intersections between one straight line and some contour lines. Using the SQL below: DROP TABLE IF EXISTS v_line; SELECT st_geomfromtext('linestring(103.492217095662 30....
0
votes
0answers
15 views

Building PostGIS 3.0 and PostgreSQL 12 with confidence on Ubuntu 18.04?

I have failed in all previous attempts to build PostGIS on macOS, centos, or ubuntu in a manner that results in the creation of the address-standardizer and the reverse geocoder. I have tried all the '...
0
votes
1answer
59 views

PostGIS & Pagination

I have a pretty standard table with a geom column and a gist index on it. I would like to have a fast pagination on this table as it contains millions of rows. The query involves a ST_Intersects: ...
1
vote
0answers
40 views

Extending OSM database with new data

I want to add a new table inside my OSM database with extra data for every restaurant, and then when retrieving the places join the table by the "osm_id", is this possible and if yes, any ...
0
votes
0answers
41 views

How can I snap irregular GPS point data to regular points of a route?

I have irregular GPS points in several shapefiles with meteorological parameters of a mobile measurement campaign. In order to compare the different shapefiles I would like to snap the irregular GPS ...
0
votes
0answers
20 views

Open Data Cube: Unable to connect to database within opendatacube Conda environment [duplicate]

I followed the steps listed at https://cubes.readthedocs.io/en/v1.0.1/install.html and did the following: Installed Python Installed PostgreSQL ssudo sh -c 'echo "deb http://apt.postgresql.org/...
-6
votes
1answer
75 views

What are “degrees” in SRID 4326 and why can't they use meters? [closed]

I want to return all the houses for sale within 500 meters from my own house. I have this query: SELECT * FROM houses_for_sale WHERE postgis.ST_DWithin(coordinates, $1, 500); It returns houses ...
3
votes
1answer
71 views

Updating table from another table with function ST_CONTAINS in PostGIS

I tried to update an attribute 'solved' of a polygon table (table name: polygon) by using an attribute 'solved' of another point table (table name: point). What I want to do is that if ALL the points ...
2
votes
2answers
140 views

Merging two isolines into one in PostGIS

How can I sum two isolines A and B (closed linestrings with values) into one isoline C with summed values on common parts? For each line string there is a value and I would like to sum on common part. ...
1
vote
0answers
30 views

Unable to connect to database within Conda Data Cube environment [closed]

I followed the steps listed at https://cubes.readthedocs.io/en/v1.0.1/install.html and did the following: Installed Python Installed PostgreSQL ssudo sh -c 'echo "deb http://apt.postgresql.org/...
2
votes
1answer
55 views

PostGIS validate the Z continuity

What is the solution to validate if a 3D linestring is always going down? All linestring nodes must have a lower value than the previous one, to check if Z values in a line are going down continuously ...
1
vote
0answers
31 views

QGIS Versioning plugin

I have created a database from QGIS Cloud free account. I have added a Post GIS layer into QGIS desktop. i have installed versioning plugin and added a group. After Selecting a Group when Hostorize ...
0
votes
1answer
24 views

using ogr2ogr to import a shapefile into a PostgreSQL/PostGIS database [duplicate]

I am trying to load a shapefile into a PostgreSQL/PostGIS databases with the following statement run within the OSGeo4W shell ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=...
4
votes
1answer
96 views

PostGIS speed up query on 9 million row polygon table

Table bl_data 9 million+ polygon rows with GIST index and the index is clustered create index ii3 on bl_data using gist(geom); CLUSTER ii3 ON bl_data; I have a query that finds records with the same ...
5
votes
1answer
108 views

When to use GiST and when to use SP-GiST index?

I'm aware of the documentation for both of the indexes, but can't decide which one to use. PostGIS documentation on these indexes Example: Let's say we have table data with the POINT attribute that ...

1
2 3 4 5
69