Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
1,355
questions
76
votes
7
answers
32k
views
Creating regular polygon grid in PostGIS
How to create, on the shape of a polygon, a regular grid of polygons/squares of an given size, in PostGIS?
I have thought about a function like Creating regular point grid inside polygon in PostGIS ...
- 983
124
votes
7
answers
59k
views
Spatial clustering with PostGIS?
I'm looking for spatial clustering algorithm for using it within PostGIS-enabled database for point features. I'm going to write plpgsql function that takes distance between points within the same ...
- 6,848
41
votes
6
answers
24k
views
Shapefile PRJ to PostGIS SRID lookup table?
I was wondering if there is such a thing as an shapefile PRJ to PostGIS SRID lookup table? Something that can translate the most standard shapefile PRJ definitions into the likely SRID.
When using ...
- 22.9k
38
votes
4
answers
13k
views
Separate polygons based on intersection using PostGIS
I have a PostGIS table of polygons where some intersect with one another. This is what I'm trying to do:
For a given polygon selected by id, give me all of the polygons that intersect. Basically, ...
- 1,742
35
votes
5
answers
44k
views
PostGIS nearest points with ST_Distance, kNN
I need to obtain on each element on one table the closest point of another table. The first table contains traffic signs and the second one the Entrance Halls of the town.
The thing is that I can't ...
- 3,728
106
votes
4
answers
38k
views
What are the pros and cons of PostGIS geography and geometry types?
My company uses geometry (the_geom) data type for storing geospatial data.
I've recently been acquainted to the concept of geography (the_geog) data type which, as I understand it, stores the SRID ...
- 6,770
70
votes
2
answers
13k
views
Acquiring ArcGIS-like speed in Postgis
I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case.
I tend to do heavy ...
- 1,611
25
votes
1
answer
15k
views
Setting up indexes for PostGIS distance queries
I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) ...
- 353
10
votes
2
answers
4k
views
How to UPDATE with LATERAL Nearest-Neighbour query?
Say I have table gps like:
CREATE TABLE gps(
gps_id serial primary key,
measured_timestamp timestamp,
geom Geometry(Point,4326),
stop_id int --Null
);
And I have a table of stops like:
...
- 3,317
34
votes
4
answers
12k
views
Bulk load multiple shapefiles into PostGIS
I've got over a hundred data layers in shapefile format that I want to upload into a PostGIS database. They are all in the same projection, but represent different data layers so they do not have the ...
- 22.9k
54
votes
4
answers
41k
views
Making spatial view in PostGIS and adding it as layer in QGIS?
In PostGIS 2.0, I am seeking an efficient way to create a spatially enabled view that is viewable in QGIS.
I followed the context of Part 2 of @Mike Toews answer to How to create a table from ...
- 22.9k
43
votes
5
answers
14k
views
Curved point-to-point "route maps" [closed]
I have recently been looking at airlines web pages that display their routes departing a certain city to all other cities they service. I would like to be able to create similar curved routes between ...
- 22.9k
61
votes
3
answers
55k
views
Changing SRID of existing data in PostGIS?
While importing my shapefile data to PostGIS, I did not select the proper Projection.
How do I now change the SRID of the data, without transforming the Coordinates?
- 40.8k
33
votes
3
answers
16k
views
Which function for creating a POINT in PostGIS?
When defining a Point in PostGIS, when do you decide to use which of the following?
ST_SetSRID(ST_MakePoint(lon,lat),4326)
ST_SetSRID(ST_Point(long,lat),4326)
ST_SetSRID(ST_GeomFromText('POINT(lon ...
- 1,667
21
votes
10
answers
33k
views
Broken packages and unmet dependency installation QGIS, PostgreSQL and postgis Ubuntu 14.04
I am aware that this could be a duplicate topic but I am struggling for a several days.
I upgraded QGIS from 2.8 to 2.18 and after that everything get broken. I couldn't use PostGIS extension on ...
- 563
14
votes
7
answers
10k
views
Calculating maximum distance within polygon in x-direction (east-west direction) in PostGIS?
I am interested in the maximum width of a polygon e.g. a lake, in east-west direction. Bounding boxes will help only in simple polygons but not in complex concave polygons.
- 141
44
votes
5
answers
27k
views
Calculating average width of polygon? [closed]
I'm interested in examining the average width of a polygon that represents the road surface. I also have the road centerline as a vector (which is sometimes not exactly in the center). In this example,...
- 16.2k
32
votes
2
answers
9k
views
How is ST_PointOnSurface calculated?
The PostGIS documentation states that ST_PointOnSurface returns "a POINT guaranteed to lie on the surface". It seems like this function could be trivially implemented to give results that satisfy the ...
- 423
47
votes
3
answers
22k
views
Reprojecting all geometries in PostGIS table?
I created a spatial table with SRID:4326. Now I want to change total projection to SRID:32644 into a new table. The old table should remain unchanged.
- 1,379
41
votes
4
answers
38k
views
Select bounding box using postGIS?
I want to create a query to select all ways and their nodes that exist within a bounding box using postGIS. The bounding box shall includes all details as osmosis "--bounding-box" command will ...
- 587
60
votes
2
answers
131k
views
Inserting point into PostGIS?
I have created one table in my PostGIS nut I cannot insert point.
What is wrong with my query?
CREATE TABLE app (
p_id INTEGER PRIMARY KEY
);
SELECT AddGeometryColumn('app','the_geom','4326','...
- 1,153
55
votes
5
answers
37k
views
SQL query to have a complete geojson feature from PostGIS?
I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature.
SELECT row_to_json(fc)
FROM ( ...
- 3,523
38
votes
21
answers
35k
views
Connecting to PostGIS database from ArcMap for display and query without ArcSDE?
How can I connect to a PostGIS database from ArcMap using ArcGIS Desktop 9.3 and later?
I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-...
- 9,371
37
votes
7
answers
25k
views
Nearest neighbor between point layer and line layer? [closed]
I've asked this question several times on stackoverflow and irc between #qgis and #postgis and I also tried to code it or implement it my self in postgis with no real answer.
Using programming (most ...
- 8,533
35
votes
0
answers
18k
views
How to use PostgreSQL/PostGIS with ArcGIS for Desktop? [duplicate]
Has anyone used PostgreSQL with PostGIS successfully?
I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog.
I've scoured the internet for resources but ...
- 451
28
votes
5
answers
54k
views
Converting MultiLinestring to Linestring with PostGIS
I have a multiline string and want to convert it to a linestring in order to use some functions that only work with linestrings. I used ST_Dump() and got a collection of my geometries. If I merge them ...
- 925
17
votes
5
answers
5k
views
Simplifying polygons to linestring?
I would like to simplify some polygons that represent rivers into linestrings. I assume that holes in polygons can be omitted. Anybody has some good idea how to simply do it?
It would be nice, if it ...
- 853
6
votes
2
answers
2k
views
Finding nearest neighbor for each point in the same table
We've got a table of approximately 300,000 points and would like to find the nearest neighbor (within 10km) to each point in the same table. The most efficient query we've found so far is below, but ...
- 637
47
votes
7
answers
12k
views
Tuning PostGIS for production environments?
What PostGIS documentation have you come across that has been helpful when tuning PostGIS for a production environment?
I would like to get together with my DBA to setup a Postgresql/PostGIS ...
39
votes
12
answers
12k
views
Creating regular point grid inside polygon in PostGIS
How to create, inside a polygon, a regular grid of point spaced x,y in PostGIS?
Like the example:
- 9,769
21
votes
3
answers
5k
views
Extrapolating a line in PostGIS
I'm trying to extrapolate from a line segment to find a point on the line but a 3rd of the way 'back', i.e. trying to find point new, given points A and B below:
Given a line, I can interpolate it to ...
- 509
16
votes
2
answers
45k
views
How to filter features in a layer?
I am trying to display certain features in a shapefile-based layer using QGIS. I have found the subset function in the properties panel, which does exactly what i want, but is too tedious given that I ...
- 161
13
votes
1
answer
867
views
Removing overlaps and keeping highest priority polygon using PostGIS
I have a multipolygon table with overlapping and non overlapping multipolygons. Where they overlap I need to keep the one with the highest (=1) priority and erase away from the lower priority (prio ...
- 64.1k
4
votes
1
answer
2k
views
Efficient way to find nearest feature between huge postgres tables
I have two tables (g1 and g2), both with around two million features. I need to know for each feature in g1, which is the closest feature from g2, with a condition. Since they are both so huge, I also ...
- 282
4
votes
1
answer
746
views
Using PostGIS to find the overall difference between two (large) polygon datasets
I need to get the Difference of an input layer with ~30k polygons and an overlay layer with ~500k polygons (of which ~200k intersect the input layer).
I am trying to learn PostGIS to do this, as it ...
- 155
2
votes
1
answer
392
views
PostGIS: ST_ClosestPoint join error
I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its ...
43
votes
3
answers
48k
views
Performing bounding box query in PostGIS?
I have a PostgreSQL table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405).
Supposing there's a geospatial index on that field, what's the most ...
- 973
42
votes
5
answers
36k
views
Fixing non-noded intersection problem using PostGIS
I'm using a PL/R function and PostGIS to generate voronoi polygons around a set of points. The function that I am using is defined here. When I use this function on a particular dataset I get the ...
- 16.2k
25
votes
2
answers
4k
views
What is the best hack for importing large datasets into PostGIS?
I have to import large Shapefiles (> 1 million records) into PostGIS, and I have been wondering about the best way to do it.
In my question I used the word "hack", instead of tool, on purpose because ...
- 737
19
votes
4
answers
7k
views
Creating one-sided buffers or parallel lines in PostGIS?
I'm looking for a way to create so-called one-sided buffers or parallel lines in PostGIS. Example: 1. buffer, 2. one-side, 3. both-sides
I've found some discussion on the mailing list from 2009 and ...
- 83.6k
14
votes
1
answer
6k
views
How to use ST_DelaunayTriangles to construct a Voronoi diagram?
(edit 2019) ST_VoronoiPolygons available since PostGIS v2.3!
With PostGIS 2.1+ we can use ST_DelaunayTriangles() to generate a Delaunay triangulation, that is a dual graph of its Voronoi diagram, and,...
- 2,214
13
votes
2
answers
6k
views
Grouping connected linestrings in PostGIS?
I have a table of streets I've selected based on a set of attributes (let's say it's speed_limit < 25). There are groups of streets that are locally contiguous; I'd like to group these sets of ...
- 12.8k
12
votes
5
answers
5k
views
Splitting lines into non-overlapping subsets based on points using PostGIS
Given a table with line geometry, and one or more points that are snapped to this line in a separate table, I would like to split each line with one or more intersecting points at each of the ...
- 8,383
12
votes
2
answers
27k
views
PostGIS Column has Z dimension but geometry does not
I am able to create a geomtry column for 3d multiline using this:
CREATE TABLE trajectories
(
gid serial NOT NULL,
vehid character varying(10),
state integer,
geom geometry(...
11
votes
1
answer
3k
views
Is the Geometry(4326) an alias for Geography datatype?
The main reference about Geography datatype seems docs/using_postgis_dbmanagement:
Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any ...
- 2,214
1
vote
1
answer
192
views
Get barrier edge id
I have a table of POINTs that represent barriers. I would like to be able to find the closest edge id on the edge graph so that I can have the routing algorithm ignore that edge (thus route around it)....
- 125
85
votes
5
answers
15k
views
Getting started with PostGIS?
I have been reading a lot about PostGIS, but I am struggling to find a good beginner's guide.
Which tutorials/books do you recommend?
54
votes
10
answers
26k
views
Selecting features that do NOT intersect in PostGIS
Using PostGIS, I just want to select points that fall outside of polygons. Ultimately this is the inverse of the ST_Intersects, as far as I can see it.
Example: I have a taxlot layer and a address ...
- 22.9k
50
votes
4
answers
48k
views
Joining lots of small polygons to form larger polygon using PostGIS?
I have the following layer using SRID 27700 in postgis:
It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
- 1,711
44
votes
4
answers
51k
views
How to insert a GeoJSON polygon into a PostGIS table?
I need to insert a polygon from GeoJSON to my PostGIS table. This is how the SQL query looks like.
INSERT INTO tablename (name, polygon)
VALUES (
'Name',
ST_GeomFromGeoJSON(
'{
...
- 1,154