Stack Exchange Network

Stack Exchange network consists of 174 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
2answers
128 views

How to create a rule on QGIS to show only features that have last week as date?

Basically I have a PostGIS layer with a date column. I only need to show features that have the last week as a value in the date column. How to achieve this? Unfortunately now() - 1 week doesn't ...
1
vote
1answer
178 views

Installation error - PostGIS on Ubuntu 18 [duplicate]

I have installed PostgreSQL 11 on Ubuntu 18 and while creating PostGIS extension it gives this error. could not load library "/usr/lib/postgresql/11/lib/postgis-2.5.so": /usr/lib/postgresql/11/...
0
votes
1answer
45 views

How do I calculate proximity for multiple points?

I have two layers, one with lots, one with subway stations (NYC). There are to scenarios: Case 1. I want to rank the lots relative to the proximity to subways. (1.a.) The closer to closest station, ...
0
votes
1answer
86 views

Uploading shapefile to PostGIS database using PHP

I want to insert a shapefile into a PostGIS database using PHP. I go through the examples and I tried the following: $command = 'shp2pgsql -s 2100 -d /D:\CHECK DATA-group\2016\Ayomi\JULY\...
3
votes
1answer
100 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
2
votes
2answers
107 views

Why is creating features with FALSE boolean fields in QGIS and Postgres failing when the fields have default values?

I'm having problems with adding rows that have boolean columns to Postgres tables from QGIS. In a nutshell, shapes with boolean fields can only be created when the booleans are set to true (updates ...
5
votes
1answer
173 views

Only union/dissolve intersecting features to save time?

I have one table of about 300k polygons, some intersecting but most do not. I need to dissolve the intersecting ones and also keep the non-intersecting ones. I only need the geometries, no attributes. ...
1
vote
0answers
31 views

Calculate average number of direct neighbors in cities in PostGIS

In need to calculate the average number of direct neighbors in pre-selected cities in PostGIS. So far, I have only found solutions to select neighbors based on distance (e.g. all neighbors within 1km ...
0
votes
1answer
39 views

Find all regions (lat, long, radius) that contain a point

A Region has a latitude, longitude (GEOMETRY('POINT')) and a radius. Using postgis, how can I query all Regions where the distance from pointParam and region.point is less than or equal to region....
1
vote
0answers
44 views

Optimal Voxel Database Schema in PostGIS

I have a database of point data in PostGIS that I'd like to work with as voxel data. For each voxel, the value would be the number of particles within its volume. Let's say for the sake of argument ...
10
votes
2answers
170 views

How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?

I'm struggling with a problem for a couple of days and realized many people also get stuck when the topic is intersections in PostGIS (v2.5). That's why I decided to ask for a more detailed and ...
2
votes
1answer
47 views

Speeding up ST_Value in PostgreSQL?

I am working on a large point file and am looking to get the raster value underneath each point. I am using: UPDATE Locations a SET image3b3_sum = ST_Value(rast, 1, geom) FROM image_sum_sum; 10 ...
-1
votes
0answers
36 views

SQL Query OSM road types in a bounding box

I have a basic command that works to query all planet_osm_line features within a bounding box (running a PostgreSQL db of the entire OSM planet file) SELECT * FROM planet_osm_line WHERE ...
1
vote
2answers
216 views

Migrating US Census GDB file to PostGIS — ERROR: tables can have at most 1600 columns

I'm working with data from the U.S. Census. Specifically, I've downloaded gdb files from here. I have the gdb file ACS_2016_5YR_TRACT.gdb on my server and I'm attempting to create a psql table with ...
1
vote
1answer
33 views

Specify database schema in MapServer mapfile

I have my mapfile. I am able to connect to my PostGIS database when my table is under the public schema. Here is my database structure. Database Ingham \__Schemas (3) \__2016 \...
0
votes
1answer
37 views

Edit and transform XML/GML files

I'm searching for an approach as someone who just started to work with geodata recently. It's about the following situation: I received several XML files. I need to perform a geometric difference ...
4
votes
3answers
137 views

Using postgis to flatten all polygons in table

I have a table with many polygons. The polygons all have a name and a value. I want to flatten these polygons so that each polygon border remains as border to the resulting polygons. Instead of ...
1
vote
1answer
26 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
4
votes
1answer
58 views

Postgis spatial index not being used

I have a query to calculate how many crimes (of a certain type) are rural and urban: select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*) from crime.street_crime c, meridian.urban ...
2
votes
2answers
43 views

How can I buffer a mulipolygon only on the coastline?

How can I achieve something similar to the below image? (Apologies for the crudely drawn diagram) Either in QGIS or PostGIS. I have a series of station grounds and I want to retain the inner ...
0
votes
1answer
108 views

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

UPDATE trajectory_maritimes a SET id_traj_day= (SELECT CONCAT( imo_ship_no, '-',EXTRACT(doy FROM arr_date_ts), '-', EXTRACT (year FROM arr_date_ts) ) FROM trajectory_maritimes b WHERE a.gid=b.gid); I ...
2
votes
2answers
47 views

Get single part polygon by their intersection in PostGIS

I have a table in PostGIS that has polygons overlapping each other. (see below an example of 4 overlapping polygons ) Someone could give me an idea, what PostGIS function I can use that give me ...
3
votes
2answers
66 views

conditional st_join and st_intersection in R (sf)

I just started working with the R sf package and am trying to rewrite a simple procedure from PostGIS to extract shared borders between polygons. In PostGIS, I usually do a "conditional" self-join ...
0
votes
0answers
84 views

Can't upgrade PostGIS on Ubuntu with Postgres 10?

I am running Postgres 10.5 (I think) on Ubuntu 14.04. I want to upgrade to PostGIS 2.2+ so I can use ST_Subdivide. I've done: apt install postgresql-10-postgis-2.4 But when I try to upgrade PostGIS ...
1
vote
2answers
108 views

How get intersection of polygons in the same table by PostGIS?

I have a single table of over 1000 polygons in postgreSQL (with PostGIS), and I have a field "evaluation_type" that only receives two values, either 'good'(green) or 'bad'(red), as you can see in the ...
1
vote
2answers
39 views

Breaking lines in PostGIS?

I have a roads table and I want to break the lines in the intersections like the v.clean tool from GRASS (I'm talking about how the break tool in the parameters works). How can I do the same process ...
0
votes
0answers
38 views

ogr2ogr merge fgdb (while importing to PostGIS)

I have millions of records and hundreds of tables in several file geodatabases.. The tables name in these FGDBs are all same but just in different areas. For instance, the Admin table, you see below,...
2
votes
0answers
38 views

postgis and st_intersects ; fail to compare a table to itself with a left join

postgresql 9.6, postgis 2.3 Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those ...
2
votes
0answers
39 views

PostgreSQL/PostGIS: Adding attributes to a table that I can use to query with

Currently I have a PostGIS database that is taking in shapefiles. However, these shapefiles are converted into tables when they are imported into the database. Each shapefile deals with a farm name, ...
0
votes
1answer
47 views

ST_Multi returning GeometryCollection in PostGIS?

I am working in Postgres 9.6. I have a table called builtup_areas: Table "public.builtup_areas" Column | Type | Collation | ...
1
vote
0answers
27 views

Use ST_startpoint and linear referencing on a self intersecting linestring that includes a road interchange loop ramp

I have a linestring (representing a route along roads) that has a self-intersecting loop. As it has a self-intersecting loop it is not valid as a linestring. But I need it to be a linestring to ...
1
vote
0answers
53 views

Is there any way to map match static points to roads using PostGIS

I'm using PostGIS and Leaflet for positioning elements on map. Because of GNSS (GPS) error, points are not always on road. Is there any way to eliminate that error and position element to road? I don'...
1
vote
1answer
57 views

Speed up intersection, union in PostGIS?

I have three tables: water, wetlands and parcels. I want to update parcel table with area that is covered by water or wetland (they can overlap). So I want to Merge water with wetland Dissolve and ...
2
votes
1answer
59 views

Create perpendicular line transects - PostGIS

I have a river network (Line with arrow as shown in image below) and I need to create perpendicular lines to the river at every 15 meters as you walk along it (see image below). Is there a function in ...
0
votes
0answers
13 views

Zero-valued bands when exporting from PostGIS using ST_AsGDALRaster

The starting point for this problem is a database pre-loaded with USGS multiband Landsat rasters. For clarity, the rasters were imported using the following commands through Python subprocess (first ...
2
votes
0answers
46 views

PostGIS geography returns area < 0.0

I am trying to convert a geometry column to a geography column and calculate the area using this query: SELECT dccode,SUM(CAST(St_Area(geom::geography,true) AS NUMERIC)) as Area FROM schema....
2
votes
1answer
32 views

How to find the closest stop point PostGIS

I have Point geometries created from lat,lon I loaded all the 10,000 stops in a certain city using gtfs i would like to query my db to find the closest stop reference to my point. the staypoint ...
3
votes
0answers
55 views

Problem with insert into PostGIS view table via QGIS

I use a PostgreSQL 9.5.14 (PostGIS) and QGIS 2.18.7. The problem is that when I try to add a new object to the View via a QGIS, the following error occurs: ERROR: column "geom" is of type ...
1
vote
0answers
48 views

Shapefile import failed in PostGIS

I am new to PostGIS/PostgreSQL and have downloaded and installed to perform a spatial join which ideally takes 27 hours in ArcGIS Pro and 19 hours by ArcPy.Hoping for a faster results via PostGIS from ...
3
votes
1answer
40 views

QGIS “ERROR” written in all fields in attribute table of a View coming from PostgreSQL/PostGIS

I have a table of around 800000 records with POINT geometry in PostgreSQL/PostGIS. When I directly open the table in QGIS, the attribute table opens fine and the fields are populated. But when I open ...
1
vote
0answers
24 views

Linux (unix) bash, import multiple fgdb to PostGIS using find & ogr2ogr command

I have multiple file geodatabases in different folders and this is the command I made so far: find -path "*/V*/*.gdb" -o -path "*/W*/*.gdb" -o -path "*/R*/*.gdb" -o -path "*/WM*/*.gdb" | while read ...
2
votes
1answer
74 views

Calculate polygon main dircetion and create line from it - POSTGIS

I would like to find out the orientation of a polygon and create a line of that direction. See image below.
1
vote
1answer
61 views

Split Polygon into separate polygons using a table of individual lines

I have been trying for a while but have not been able to split a polygon at multiple locations, using a table of lines, to get what is on the right side of the above image. The best I can achieve is ...
0
votes
1answer
25 views

Mapserver displays blank image of POINT layer data extracted from PostGIS database [duplicate]

I am trying to display a couple of lat/long points with Mapserver, which are stored in a PostGIS database. Here is my Database setup: CREATE TABLE test( gid SERIAL NOT NULL PRIMARY KEY,...
0
votes
2answers
37 views

Using original shapefile vs osm for routing in South Australia using pgRouting?

I'm looking at building a simple routing application using pgRouting. I'm running on Windows 10 at the moment but could migrate to Windows Server at some point. I'm trying to figure out how best to ...
0
votes
0answers
22 views

Filling polygon holes using PostGIS? [duplicate]

I have a polygon that I need to fill in the "holes" that are less than X meters squared in area. I can find the area of a polygon, but is there a way to find the areas of the "holes" and if they are ...
2
votes
1answer
92 views

Unicode encoding problem in ogo2ogr GeoJSON export from PostGIS

I have few tables those have some unicode value of different language. When I am trying to export the data from PostGIS to GeoJSON using ogr2ogr the returned GeoJSON showing wrong unicode language ...
3
votes
1answer
40 views

Can't get ST_MapAlgebra to return fractional values

I have a 4-band raster dataset that I have imported into a Postgres/PostGIS table using raster2pgsql. I am Using ST_MapAlgebra() with a callback function to perform some band math on the dataset. I ...
2
votes
2answers
51 views

Using st_intersects between tables in PostGIS?

I have two tables: filter_polygon and myGeometries In both tables I have geometry columns. myGeometries contains > 100 mln of rows. In both tables I have geo indexes like: CREATE INDEX ...
2
votes
1answer
51 views

Selecting polygon id's if their centroid falls in another polygon using PostGIS?

I'm trying to select polygons which centroids fall in another polygon: SELECT smaller_polygons.id FROM big_polygon LEFT JOIN big_polygon smaller_polygons ON ST_Within(ST_Centroid(smaller_polygons....