Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
3,433
questions
1
vote
0answers
29 views
Combine LiDAR data with OpenStreetMap
I have already built Ubuntu for OpenStreetMap and I have some LiDAR data file in LAS/LAZ format. Is it possible to make LiDAR data appear in 2D and be a part of OpenStreetMap? If it is, how?
I have ...
2
votes
1answer
49 views
Find COUNT of multiple tables that ST_INTERSECT a main table
I have 3 tables. 1 polygon main table polytable (400,000 polys with 1000 unique names in the polyname column), and 2 point tables pointtable1 (millions of points) and pointtable2 (1000s of points). I'...
1
vote
1answer
35 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
36 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
19 views
TypeError: Invalid parameters given for Point initialization.on Django
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 leaflet map.But when I click ...
0
votes
0answers
14 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
137 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
26 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
34 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
47 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
88 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
33 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
30 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
46 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
25 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
72 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
68 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
27 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
37 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
185 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
46 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
31 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
16 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
78 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
31 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
32 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
25 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
98 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 ...
