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.

93
votes
6answers
40k 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 ...
85
votes
9answers
14k 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?
82
votes
4answers
27k 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 ...
62
votes
2answers
11k 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 ...
59
votes
7answers
12k views

Creating Vector Polygons with rendering performance like GISCloud?

I have been looking for a solid solution which would allow me to create a web map and overlay vector polygons without taking forever to load such data with the goal of allowing me to make each polygon ...
57
votes
5answers
19k 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 How to create regular point grid inside a polygon in ...
50
votes
5answers
115k views

How to get coordinates from geometry in PostGIS?

This line: SELECT geom FROM myLayer.geom will return a hex value, i'm wondering how would it be possible to get decimal values, a human-readable coordination?
49
votes
4answers
30k 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 ...
48
votes
2answers
95k 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','...
47
votes
7answers
10k 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 ...
47
votes
4answers
32k 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 ...
46
votes
2answers
34k 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?
45
votes
3answers
9k views

What is the purpose of PostGIS on PostgreSQL?

PostgreSQL already supports spatial data types, operators and indexing. What does PostGIS provide exactly that made it necessary to exist as an extension to PostgreSQL? Why don't we all just use ...
43
votes
2answers
10k views

Meaning of Simplify's tolerance parameter

The standard parameters of Douglas-Peucker's simplify algorithm are geometry and tolerance (e.g ST_Simplify in PostGIS). What's the meaning of the tolerance parameter? I know that the bigger the value,...
40
votes
3answers
34k views

How can I get a shapefile from a postgis query?

My data is in a PostGIS database. I would like to generate a shapefile from a query. How can I do that?
39
votes
5answers
15k 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,...
39
votes
5answers
11k 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 ...
39
votes
6answers
20k views

Migrating geodatabase data into PostGIS without Esri apps?

Can you tell me how I can load geodatabase data into PostGIS without having to install Esri apps? The solution I found online talked about using ArcGIS to convert GDB to SHP and then importing SHP ...
38
votes
3answers
15k 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.
38
votes
8answers
14k views

Select features that do NOT intersect in PostGIS

This seems to me like such a simple question (and it probably is) yet I cannot seem to find an example that gives me the answer. Using PostGIS, I just want to select points that fall outside of ...
37
votes
2answers
3k views

What coordinate system should be used to store geography data for celestial coordinates?

I am doing an astronomy project. I want to have the information about our images stored in a spatially enabled database. This, I would think, should be a very easy special case for GIS functions ...
36
votes
6answers
19k 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 ...
36
votes
5answers
34k views

Why does PostGIS installation not create a template_postgis?

I went this route for installation of PostgreSQL and PostGIS: PostgreSQL using the standalone KyngChaos, PostGIS install from KyngChaos as well... pgAdmin3 from PostgreSQL Tools Now when I launch ...
36
votes
3answers
31k views

Best GIS system for high performance web application - PostGIS vs MongoDB

I am working on a web/mobile application based on location data. Since i am already familiar with MongoDB, i found the geospatial indexing of mongo is quite suitable for my needs. As i am mainly ...
36
votes
5answers
19k views

How best to fix a non-noded intersection problem in 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 ...
36
votes
4answers
29k 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 ...
36
votes
1answer
32k views

Choosing SRID and what is its meaning? [closed]

I'm new to GIS, in PostGIS, and I'm struggling with the SRID concept. How do you choose a SRID for a database column? This is making me crazy. Why do I need to choose a SRID? What's its meaning? ...
35
votes
21answers
29k 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-...
35
votes
7answers
17k 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 ...
35
votes
0answers
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 ...
34
votes
2answers
14k views

Looking for fastest solution for Point in Polygon analysis of 200 million points [closed]

I have a CSV containing 200 million observations with the following format: id,x1,y1,x2,y2,day,color 1,"-105.4652334","39.2586939","-105.4321296","39.2236632","Monday","Black" 2,"-105.3224523","39....
33
votes
4answers
11k 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, ...
33
votes
2answers
28k views

How do I check which version of GDAL I have and how many?

I have installed in my computer the following software: PosgresSQL 9.3.2.3 PostGIS 2.1.1.1 QGIS 2.0.1 Dufour (this one installed through the OSGeo4w package) QGIS 1.7.0 Wroclaw As far as I know all ...
32
votes
4answers
9k 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 ...
30
votes
2answers
27k views

Changing geometry type from Point to Multipoint within existing table in PostGIS?

Is there a PostGIS function that can change geometry type for an existing table? We need to change from POINT to MULTIPOINT. The table will be empty when we change the geometry type and we cannot ...
30
votes
4answers
27k 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( '{ ...
30
votes
3answers
9k 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 ...
29
votes
1answer
26k views

How to convert/typecast from Polygon to Multipolygon in PostGIS?

Is there any way to typecast Polygons to Multipolygon type? For example, I have created a circle using ST_Buffer() on a point. The return type of that function is of Polygon type. The problem is that ...
28
votes
8answers
7k views

How to create regular point grid inside a polygon in Postgis?

How to create, inside a polygon, a regular grid of point spaced x,y in postgis? Like the example:
28
votes
4answers
5k views

When should you NOT use a spatial index?

I am asking this because I was mainly working with Oracle but for the past year I've been doubling with PostGIS and SQLServer 2008. Most spatial functions in Oracle won't work without a spatial index ...
27
votes
4answers
16k 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 ( ...
27
votes
9answers
15k views

Working with PostGIS data in R?

I work with R almost all the time, and now I am using it for doing spatial data mining. I have a PostGIS database with (obviously) GIS data. If I want to make statistical spatial analysis and plot ...
27
votes
2answers
42k views

Adding shapefiles to PostGIS database

I was able to create a Postgresql database and then I executed, createlang plpgsql geospecies psql -d geospecies -f postgis.sql psql -d geospecies -f spatial_ref_sys.sql So now can I directly import ...
27
votes
6answers
11k views

How can I update ogr2ogr to include the PostgreSQL driver?

I'm trying to use ogr2ogr to upload a shapefile to a remote PostGres installation. When I ran this command: celenius:~ celenius$ ogr2ogr -f PostgreSQL PG:"host=255.34.00.00 user=postgres dbname=mydb ...
27
votes
6answers
11k views

Dealing with data that spreads across UTM Zones?

I have data that spreads across two UTM Zones GDA/MGA94 UTM Zone 55 and 56. I have layers such as a cadastre, roads, pipelines etc where I want to store measurements like lengths and areas in metres ...
27
votes
4answers
17k views

How to get the nearest point on a linestring to a given point?

I've been using PostGIS for a long time now but never had to use the LINESTRING geometry ...! :) Here's what I would like to do : I have a table of linestrings (representing streets of a given city, ...
27
votes
5answers
14k views

Optimizing osm2pgsql imports for OSM data

I'm currently building an instance on EC2 on which to import the entire Planet.osm snapshot of the whole Earth's worth of data for some projects we're working on. I've spun up a large Ubuntu x64 ...
26
votes
6answers
24k views

QGIS, Postgis: Geometry type does not match column type

I'm trying to import some polygon shapefiles to Postgis in QGIS via SPIT. One of them can't be imported and returns this error: ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)...
24
votes
5answers
4k views

Alternatives to ogr2ogr for loading large GeoJson file(s) to PostGIS

I have a 7GB GeoJson file that I would like to load into a PostGIS database. I have tried using ogr2ogr but it fails because the file is too big for ogr2ogr to load into memory and then process. ...
24
votes
2answers
15k views

In PostGIS is it possible to create a View with a unique ID?

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table? Edit: Sorry I should have included this in the original post. I ...