Questions tagged [postgis]

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

Filter by
Sorted by
Tagged with
73 votes
7 answers
29k 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 ...
user avatar
116 votes
7 answers
55k 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 ...
user avatar
  • 6,618
40 votes
6 answers
23k 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 ...
user avatar
  • 22.4k
37 votes
4 answers
12k 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, ...
user avatar
  • 1,674
33 votes
5 answers
40k 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 ...
user avatar
  • 3,480
102 votes
4 answers
35k 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 ...
user avatar
  • 6,554
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 ...
user avatar
  • 1,611
8 votes
2 answers
3k 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: ...
user avatar
  • 3,207
33 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 ...
user avatar
  • 22.4k
43 votes
5 answers
13k views

Curved point-to-point "route maps"

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 ...
user avatar
  • 22.4k
22 votes
1 answer
13k 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) ...
user avatar
54 votes
4 answers
39k 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 ...
user avatar
  • 22.4k
58 votes
3 answers
49k 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?
user avatar
44 votes
5 answers
24k 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,...
user avatar
  • 15.9k
31 votes
1 answer
7k 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 ...
user avatar
  • 413
14 votes
7 answers
9k 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.
user avatar
40 votes
4 answers
36k 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 ...
user avatar
  • 577
46 votes
3 answers
19k 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.
user avatar
32 votes
3 answers
14k 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 ...
user avatar
  • 1,567
20 votes
10 answers
31k 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 ...
user avatar
  • 533
49 votes
4 answers
33k 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 ( ...
user avatar
38 votes
21 answers
34k 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-...
user avatar
  • 9,311
59 votes
2 answers
122k 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','...
user avatar
  • 1,143
37 votes
7 answers
24k 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 ...
user avatar
  • 8,432
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 ...
user avatar
  • 853
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 ...
user avatar
  • 451
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 ...
25 votes
4 answers
47k 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 ...
user avatar
  • 887
20 votes
3 answers
4k 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 ...
user avatar
  • 499
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 ...
user avatar
  • 272
39 votes
11 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:
user avatar
  • 9,733
42 votes
5 answers
33k 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 ...
user avatar
  • 15.9k
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,...
user avatar
  • 2,084
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 ...
user avatar
  • 81.9k
13 votes
2 answers
5k 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 ...
user avatar
  • 12.4k
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 ...
user avatar
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 ...
user avatar
1 vote
1 answer
155 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)....
user avatar
  • 115
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?
29 votes
1 answer
40k views

Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

I am new to GeoJSON. I have a GeoJSON features collection as shown and would like to store it in postgres table (testtable). My postgres table has a serial id and geometry column. { "type": "...
user avatar
  • 293
49 votes
4 answers
44k 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 ...
user avatar
  • 1,685
51 votes
9 answers
23k 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 ...
user avatar
  • 22.4k
41 votes
4 answers
45k 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( '{ ...
user avatar
  • 1,104
36 votes
3 answers
42k 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 ...
user avatar
  • 893
23 votes
3 answers
7k views

How can I merge/collapse nearby and parallel road lines (eg. a dual-carriageway) into one single line?

I have a shapefile from OSM which contains all the roads within a city. Larger roads (like dual-carriageways) seem to have 2 parallel lines. I'd like to collapse / combine those into 1 line so I can ...
user avatar
  • 1,025
32 votes
2 answers
18k views

What is the Difference between Geometric and Geographic columns? [duplicate]

I am a beginner in GIS and Learning PostGIS. I came across this two terms Geom and Geog, Could anyone explain in brief. What is the best to use with map data and Do all functions support both Geom and ...
user avatar
  • 799
20 votes
2 answers
17k views

Counting Points in Polygon with PostGIS

I've got a simple problem: I want to count the number of points within a set of polygons. I have a SQL already but it only gives back the gid of the polygone that actually contains points. My tables: ...
user avatar
  • 1,068
12 votes
4 answers
6k views

In PostGIS: how to split linestrings into their individual segments?

I have one table with many linestrings and I want to make a new table containing the individual segments of all the linestrings, no matter if they are grouped line per line or not. This is my table, ...
user avatar
10 votes
2 answers
4k views

How can I convert geomagnetic coordinates to geographic coordinates without doing the math myself?

I have some data which are in geomagnetic coordinates; that is, they are latitude and longitude, but in reference to the magnetic north pole in Canada rather than the geographic north pole. As you ...
user avatar
  • 602
8 votes
1 answer
2k 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 ...
user avatar
  • 2,084

1
2 3 4 5
27