Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

2
votes
0answers
23 views

Creating map of travelling salesperson problem solution with pgrouting?

I have created the "ways" (=edges) and "ways_vertices_pgr" (=nodes) tables from OSM road data with osm2pgrouting in PostGIS. I work with this data in QGIS. Given a set of ids from the "...
3
votes
2answers
36 views

PostGIS st_makeline extra columns in result

From a table of point data, there are some groups of points that can define a line (cross sections of a water course). 'cross_section' is the name of the table with the point data, geometrypoint the ...
0
votes
0answers
22 views

QGIS project file saved to PostGIS – issue with users' privileges

I have multiple QGIS projects with PostGIS layers saved to different schemas (new feature in QGIS 3.x). I created various users with different privileges, but for the sake of this problem, let's ...
2
votes
2answers
41 views

Export selected PostGIS features to a new PostGIS layer in QGIS?

I am trying to export a selected set of features from a PostGIS layer into a new layer. But when I go to the context menu for the later and choose Export -> Save Selected As ..., there is no option ...
2
votes
0answers
11 views

QGIS Server Plugin Loading Problem: 'unexpected type 'sip.wrappertype'

I'm using 3.6 qgis on the Ubuntu 18.04 server. I made a plugin for limiting requests on our WMS server. I'm having troubles loading the plugin I get always error: Traceback (most recent call ...
1
vote
2answers
29 views

PostGIS line in polygon performance (ST_Intersects)

I am running an analysis to find lines that is covered by a polygon. I have to create the polygon from a road network (hence the buffer and union) because the the lines are not exactly equal. The ...
0
votes
0answers
35 views

How to generalize or simplify the multipolygon in PostGIS without missing any polygons?

By following the rules from this site, I got the output but some polygons do not come within the simplified polygon. I think the problem is with topology. Is there any solution?
2
votes
0answers
27 views

QGIS 3.4 - Credentials to PostGIS connection constantly requested when panning the map

I am using PostGIS databases linked to QGIS 3.4 with credentials set for all users, in other words user-authenticated workspaces. Note: The credentials are not stored within the project file or setup ...
2
votes
0answers
18 views

Load PostGIS Layer with BLOB and use the form widget for binary fields (QGIS 3.6) not working

I have tried to load a PostGIS layer containing a binary data field (BLOB, bytea) and then use the new form widget for binary fields (https://qgis.org/en/site/forusers/visualchangelog36/index.html#...
1
vote
1answer
41 views

PostGIS: Running multiple intersections within a single SQL query?

PostgreSQL 11 + PostGIS, on Windows 10. I'm working with a PostGIS-enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the ...
3
votes
2answers
84 views

Re-order multipart polygons in PostGIS

I have a table with multpart polygon geometry with coordinate system EPSG:4326. I can identify, what are geometries with more than 1 polygons in it. I want to re-order it in a way that the largest ...
0
votes
0answers
18 views

pgRouting TSP with eventual order constraints?

Is-it possible to run Travelling Sales Person (TSP) in pgRouting (I've got 2.3... installed) with constraints? A general TSP algorythm or those I've identified in pgRounting solve the TSP problem ...
1
vote
0answers
37 views

How to create a function for getting a largest polygon from 2 polygons using case statement in PostgreSQL?

CREATE OR REPLACE FUNCTION public.largest_polygon1( geom1 geometry, geom2 geometry) RETURNS geometry LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin Return Case ...
1
vote
1answer
29 views

How to calculate extreme point (most north or east etc) of geography(polygon) type in PostGIS

I have a table which include geography(polygon) type. CREATE TABLE place ( name varchar(100), geom GEOMETRY(POLYGON, 4326) ); insert into place SELECT 'place1', ST_GeomFromText('POLYGON((-71....
1
vote
1answer
34 views

PostgreSQL - Creating DB, User privileges [on hold]

I created a database named "wchiba" in postgres using : create database wchiba; Then I create a user : create a user saifeddine with password "0000"; Then I grant privileges : grant all on ...
0
votes
1answer
21 views

password authentication failed for user “ASUS”

I'm creating table in PostgreSQL using these commands: shp2pgsql -s 32632 watershed.shp watershed > watershed.sql psql -f watershed.sql cambridge psql -f watershed.sql cambridge But I had ...
1
vote
0answers
19 views

Using CSW for GeoServer PostGIS database

I am quite new to GeoServer and general OGC fundamentals. I have GeoServer set up and I set up a PostGIS data store which links to a database I have already been able to make successive calls using ...
2
votes
1answer
29 views

raster2pgsql: queries on imported raster are NULL

I loaded a tif-file of some soil data into postgres with: raster2pgsql -c -I -F -C -r -M -s 31467 -t 200x200 soil_file.tif gis.soil_map | psql -U user -d database -h localhost -p 5432 which worked ...
1
vote
1answer
46 views

Type geometry does not exist PostGIS [duplicate]

I am trying to add a geometry column to an existing table in PostgreSQL inside a plpgsql function using EXECUTE format('ALTER table %I add column geom geometry(Point, 102003)', target_table); Its ...
4
votes
0answers
59 views

Increasing PostGIS/PostgreSQL memory to avoid Out Of Memory (OOM) error?

This is a follow-up question to my earlier question about the memory management of some PostGIS/PostgreSQL functions: How do PostGIS functions handle buffers and external storage? As suggested by ...
1
vote
1answer
43 views

Sending geographic data from Postman?

I am trying to create a GIS app using Java, Spring and Hibernate. I send a JSON from Postman like this: { "pointName":"any location", "geom":"POINT(-71.060316 48.432044), 3857" } The JSON ...
0
votes
0answers
29 views

How would I adjust the borders of America to fit into the state of Colorado? [closed]

I would like to adjust the borders of the United States of America so that they are rectangular They should fit into the state of Colorado both with oceans included and without. The oceans included ...
0
votes
0answers
13 views

pgRouting plugin error

I have used osm2po to create routing data after load the data to database(postgresql) then from it to QGis.when using the pgrouting plugin the error appears: 2019-05-16T12:43:17 WARNING ...
0
votes
1answer
105 views

How to create a function for finding the largest of 2 polygons by area in PostGIS?

I tried my level in creating the function but it shows error while running. I have enclosed the code here : create function largest_poly (geom1, geom2) returns geometry as $largest_poly$ begin from ...
1
vote
1answer
63 views

Mapping land plots which contain two buildings

I need to show land plots in QGIS that have two buildings. My query only shows buildings which are placed on land plots. Create or Replace VIEW dvi_budivli_view AS Select "budivly".*, "dilyanki" ...
1
vote
2answers
47 views

shp2pgsql command to import shapefile [closed]

I have a problem loading my shapefile named watershed and link it to my table in postgresql named watershed as mentioned here: shp2pgsql -I -s 32632 C:\Users\ASUS\Desktop\master_PEER\DATABASE\...
-1
votes
0answers
20 views

Tableau import Postgis doesn't return in geometry [closed]

I failed to load table from PostGis to Tableau. It returns the following. Kindly help. Fairuz,
0
votes
1answer
52 views

Citing PostGIS in formal publications?

I have worked a piece of academic work using the PostGIS extension on PostgreSQL (pgadmin). I have done a lot of work include buffer generation and data processing. How do I cite this software for ...
1
vote
0answers
27 views

Renaming duplicates with any vacant value within a certain interval

The values of my column of interest (col1) should range from 0-999. I'm looking for a way to assign one of each duplicate a new value that lies within my this range 0-999. Only features that are ...
2
votes
0answers
43 views

Split Line by points with PostGIS

I'm having some troubles trying to split a Linestring with 3 points. My line is a circular route from a Garage (GA) to a pick up (PU) point, then a drop off (DO) point, and return to garage (img1) ...
2
votes
1answer
46 views

Improving Mapnik render time

I'm adding polylines (elevation curves) to a world map. Said polylines are stored in a PostgreSQL database from gdal_contour tool. The geometry columns (LineString, 3857) has a GIST index and a ...
2
votes
0answers
21 views

Point cloud query is very slow and not using parrallel scan or more memory

The following PostGIS query is running very slow (> 1 day for about 1 million records): UPDATE test.table1 SET area= ST_Area(PC_EnvelopeGeometry(pa)) WHERE area is null or area <> ...
1
vote
0answers
22 views

Bounding box algebra in PostGIS?

Are there efficient ways of finding the union, intersection, and difference between two bounding boxes (box2df)? I know that this can probably be done by first converting the bounding box (BOX2DF) to ...
2
votes
1answer
18 views

How to activate “explode hstore field” in QGIS 3.6

In How to categorize features based on hstore values in QGIS? somebody said: "In QGIS 3.6, you can use the "explode hstore field" which will create one field for each keys in the hstore field" but i ...
1
vote
1answer
35 views

Storing date in m-dimension of PostGIS geometries

Do you know if it is possible to assign a date (whatever the format) as the 4th dimension to a geometry in PostGIS? If so, does it recognize it as a date?
0
votes
0answers
18 views

How to update geometry in PostGIS for features that are in an ArcGIS enterprise geodatabase (SDE)?

UpdateGeometrySRID is not working for some reason, I think this may be due to it being an enterprise geodatabase? I also tried the manual version of UpdateGeometrySRID, where you alter the table and ...
1
vote
1answer
58 views

Hausdorff distance (ST_HausdorffDistance) extremely slow for polygons?

I have a simple SQL statement for computing the Hausdorff distance between two polygons of the form: SELECT ST_HausdorffDistance(geom1, geom2); , which is taking a long time (for a pair of polygons ...
1
vote
1answer
31 views

Table to Distance Between in PostGIS

I am a new user in PostGIS. I need to create a table with the ID_DIR of table A, ID_SQ of table B and have a new column called "Distance" that would be the distance between the ID_DIR and the ID_SQ in ...
1
vote
0answers
27 views

Return County name from address using PostGIS?

I'm using PostGIS to geocode address data like this: SELECT pprint_addy(addy), ST_AsText(geomout) FROM geocode('Chicago, IL'); I've loaded all the TIGER data for each US state from the US census ...
1
vote
1answer
46 views

GIST index not used by query optimizer on geometry field

I'm trying to add elevation curves on an OpenStreetMap tile server I'm currently building. I created a table in PostgreSQL to store the shapes of the whole world. Structure was defined by gdal_contour ...
2
votes
2answers
45 views

PostGIS: Calculating the shortest distance between one point and MULTILINESTRING objects

I have a table with MULTILINESTRING objects and I want to build a query that outputs the shortest distances between every MULTILINESTRING object and a specific point (the same for all records) using ...
2
votes
1answer
29 views

Getting “missing FROM-clause” error when adding ST_Intersects constraint to PostGIS table

I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added. Obviously dams have to cross a river ...
1
vote
0answers
78 views

Uncompressed vs Subdivided: which one offers better PostGIS INTERSECTS performance?

In my PostGIS I have a point table (roughly 51 millions rows) and a multipolygon table. I need to find a polygon label/ID for every single point in my point table (roughly 51 millions rows) in PostGIS....
1
vote
1answer
35 views

Distance between two point layers

I have a main addresses point layer and a areas point layer. Both layers have XYZ coordinates in the attribute table. I need to create a table based from the addresses layer which calculates the 3D ...
0
votes
0answers
15 views

Submit form in leaflet popup makes blank page

I have a problem with a leaflet popup. I made a form in it and when I try to send form with jquery & AJAX to postgis, data inserts correctly but blank page loads. How to update popup with response ...
0
votes
0answers
32 views

Fitting many rectangular shaped polygons into larger irregular shaped polygon in PostGIS?

I am looking to try and find the optimal fit for a number of rectangular shaped polygons into a larger irregular shaped polygon and I haven't found much information about how this could be achieved in ...
0
votes
1answer
32 views

ST_SnapToGrid doesn't work properly, e.g. 41.94186153740355 -> 41.941861499999995

I'm using ST_SnapToGrid: ST_SnapToGrid(somePoint, 0.0000001) and I get strange result on many points, such as: - POINT (21.619820510769063 41.94186153740355) -> POINT (21.6198205 41....
1
vote
0answers
30 views

Geometry type (Point) does not match column type (LineString)

I'm using PostGIS 2.5.2 on PostgreSQL 10, and I'm trying to build a function which would split a LineString into portions, depending on points positionned close to it. I'm getting a Geometry type (...
0
votes
0answers
20 views

Box Select using leaflet by modifying box zoom with turf intersect

I have been trying to select multiple polygons using box select. I have modified box zoom for this purpose and included turf.js for intersect function. The problem is if i select a particular area and ...
1
vote
0answers
38 views

Convert meters to radians

I'm doing a project in PostGIS where I need to know what is the conversion of meters to radians. I would like to know how to do or what to look for to carry out the doubt.