Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
4 votes
2 answers
158 views

PostGIS Maximum Inscribed Circle

I'm attempting to utilize the PostGIS function ST_MaximumInscribedCircle() but having some trouble interpreting the example. How can I feed the geometry into a from clause to retrieve the center and ...
user avatar
  • 1,051
4 votes
1 answer
71 views

PostGIS Nearest neighbor really slow

I'm trying to set a link between two tables (db1 and db2) with a nearest neighbor approach and some condition checking. SELECT id, label_shop, linked_rows.* FROM shopitem.shopitem_database shop LEFT ...
user avatar
  • 71
2 votes
0 answers
11 views

How to derive raster values fast into the gird?

I have a grid that has the same dimension as a raster (Sentinel 2 imagery) which is 10 x 10 km. In addition inside the grid, I have 1000 rows and 1000 columns which make each cell 10 m resolution. ...
user avatar
0 votes
1 answer
8 views

Osm2pgsql data dump in specific schema not working

While using osm2pgsql 1.6 i am trying to dump PBF into specific schema of postgres DB using following commands using docker. (i am setting database properties in docker env) docker run -v /mnt/ne-...
user avatar
1 vote
1 answer
18 views

Wrong schema while importing data via with ogr2ogr into postgresql database and using dblink

I have the following issue: I have a table on a PostgreSQL database which triggers its changes via dblink to another table in a different data base. The trigger function looks as follows: CREATE OR ...
user avatar
  • 331
0 votes
0 answers
20 views

How to correctly/efficiently store Sentintel-2 imagery with 100 x 100 km dimension?

I am trying to store 100x100 km Sentinel-2 tile imagery into the PostgreSQL table. I have only these bands: B2, B3, B4, B8, and B11, resulting in a 2.5 GB GeoTIFF file. Consequently, due to the large ...
user avatar
2 votes
1 answer
27 views

Reducing shapefile size from PostgreSQL using ogr2ogr

I have a PostGIS database created from an old AutoCAD system. Because of this, every column is type varchar(254), even though actual data lengths are small. I need to export tables as shapefiles, and ...
user avatar
  • 21
-1 votes
0 answers
12 views

Linestring that crosses a polygon the points where the line length intersects with the polygon in km + m

I have to identify a linestring that passes through a polygon and when the linestring intersects the polygon boundary at the input and output I have to give km + m input and km + m output from the ...
user avatar
0 votes
0 answers
25 views

PostGIS error execute query [closed]

I'm trying to run a Query that does a larger operation in the PostGis database: The code is: $q = "SELECT ply.ogc_fid AS pid, lns.ogc_fid AS lid, ST_LineSubstring(...
user avatar
3 votes
1 answer
26 views

How can I get the closest linestring in my database to the linestring drawn by some user, following a path?

I have to take a geometry drawn by an user and return the geometry in my database that represents the drawing. I tried in PostGIS In my database I have a column 'way' with the geometry that I want to ...
user avatar
4 votes
1 answer
30 views

Pass a variable parameter to pgr_dijkstra (pgRouting) Edges SQL

I need to pass a parameter to Edges SQL in a pgRouting function, for example pgr_dijkstra. I try something like: PREPARE test (int) AS INSERT INTO elab.test_f SELECT * FROM pgr_dijkstra( 'SELECT id,...
user avatar
  • 513
2 votes
1 answer
53 views

PostGIS st_intersection returns only the lines that are inside the polygon

I have two polygons called 1, 2. I have a linestring that crosses the two polygons but does not remain only inside them. What I'm trying to do: Find the linestring that is from A to D in the case of ...
user avatar
3 votes
1 answer
32 views

GRASS GIS: Execute SQL query and save output

I'm trying to find a way in GRASS GIS to execute a complex SQL query (with JOINs) and save it. SELECT a.id, a.geom FROM schema01.table01 a JOIN schema01.table02 b ON a.geom && b.geom AND ...
user avatar
3 votes
2 answers
34 views

Convert linestringz to linestringm using alter column?

I am attempting to import linestring data with or without m values, then test whether the data has m values, then add m values to the geometry if they are missing. So far, I have working tests for ...
user avatar
  • 6,400
5 votes
2 answers
72 views

Calculating cost values in pgRouting using ST_DWithin

I have a table where there are two geometries: geom_way (for routing roads) and (osm_meta) which is a triangulation from polygons. I use ST_DWithin function (between both geometries) with a distance ...
user avatar
2 votes
1 answer
85 views

Merge multiple tables (100 tables) into a new table in PostGIS

I am looking to merge a large number of individual tables into a new table in PostGIS. I have almost 100 "identical" tables so I wanted to do it automatically. There is a good answer here ...
user avatar
0 votes
0 answers
42 views

PgRouting withPoints from point to point

How create points_table for pgr_withPoint function? I know that table must have: pid, edge, fraction, side. For example I have point A(lat,lon) and point B(lat,lon), and I want to get route from A to ...
user avatar
  • 1
0 votes
0 answers
19 views

Connecting to RDS PostgreSQL with QGIS with SSH?

I’m still kind of learning and this is new to me so forgive me if I’m using the wrong terminology or wrong tools. I’m trying to connect a local client (QGIS) to a PostgreSQL database hosted on an RDS ...
user avatar
  • 35
3 votes
3 answers
99 views

Remove only certain squares from grid in PostGIS

So I have this grid (I create this from oriented enveloping the brown polygons and then buffering) create temp table test_squares as select test_fishnet.poly from test_fishnet, polys where ...
user avatar
  • 161
1 vote
1 answer
15 views

ogr2ogr list of layers created

Using a command such as the following:- ogr2ogr -f PostgreSQL "postgresql:..." \ -lco SCHEMA=my_schema \ foo.xlsx on a Excel sheet with multiple tabs will create multiple tables in schema ...
user avatar
0 votes
0 answers
46 views

Unable to connect to a PostgreSQL database

I've been accessing a PostGIS database in QGIS I created on an old laptop and now trying to reconnect to it with a new laptop. I've installed PostgreSQL 14 whilst the database I want to connect to was ...
user avatar
  • 2,089
0 votes
0 answers
23 views

How to get all the points ina bound from PostGIS

I have a table that consists a column names geos and is of Linestring field but I need to fetch the data using bounds. But not sure how to do it. I tried below query but nothing is coming. SELECT NULL ...
user avatar
  • 101
1 vote
0 answers
27 views

Create db connection from ArcMap or ArcGIS Pro to a regular non-spatial PostgreSQL database

When I attempt to create a database connection via ArcMap or ArcGIS Pro to a pure tabular PostgreSQL database, I keep getting insufficient permissions error. I know it is not a permissions issue. Is ...
user avatar
5 votes
2 answers
59 views

Removing certains routes from pgrouting output table

I have used pgRouting to generated multiple many to many traffic routes different points of interest. I have used st_Dwithin to put a catchment area on the routing alogirthm, however the agg_cost(...
user avatar
2 votes
0 answers
17 views

Retrieve properties for a feature in MVT from JSONB column?

TL;DR Check the last UPDATE section. We store all of our geometries in a single hybrid PostGIS table and corresponding properties in a single JSONB column. Properties can be anything and differ for ...
user avatar
  • 307
2 votes
1 answer
32 views

How to convert raster file into a series of SQL commands

I am trying to save raster data to the PostgreSQL database. I know that there exists raster2psql.exe, but this command limits me from adding additional columns to the table. For this reason, I would ...
user avatar
4 votes
1 answer
113 views

Convert bytea to geometry column with PostGIS

I am trying to create a geometry column from bytea. I imported OSM data using ogr2ogr using the command ogr2ogr -f PostgreSQL -nln bahamas_multipolygon PG:"dbname=openstreetmap user=postgres ...
user avatar
0 votes
0 answers
33 views

Update OSM database failing

I'm trying to udapte a OSM database that I just imported. After following the steps https://ircama.github.io/osm-carto-tutorials/updating-data I don't manage to pass the first udpate. I always get the ...
user avatar
1 vote
1 answer
30 views

Save xml CityModel files to PostGIS

I'm trying to save CityModel xml files to our PostGIS Database with the files looking like this: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <core:...
user avatar
  • 151
2 votes
1 answer
107 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 ...
user avatar
  • 203
2 votes
1 answer
34 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 ...
user avatar
3 votes
1 answer
56 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 ...
user avatar
1 vote
0 answers
32 views

GeoServer JDBC connect error

I installed a clean version of the GeoServer 2.20.4 on Windows, I create "User Group Services" JDBC to connect to the PostgreSQL database and add a user, create "Authentication ...
user avatar
  • 11
2 votes
1 answer
58 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 ...
user avatar
1 vote
0 answers
39 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 ...
user avatar
3 votes
1 answer
87 views

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

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}....
user avatar
  • 63
3 votes
1 answer
71 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,121
3 votes
1 answer
60 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
  • 267
5 votes
2 answers
65 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,121
3 votes
1 answer
56 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
  • 31
0 votes
0 answers
58 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,534
2 votes
0 answers
21 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
36 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
1 vote
1 answer
36 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
1 vote
1 answer
57 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
1 vote
0 answers
28 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
63 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
32 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
1 vote
1 answer
56 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
  • 113
0 votes
0 answers
34 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,748

1
2 3 4 5
79