Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,547
questions
104
votes
7answers
47k 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 ...
89
votes
4answers
30k 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 ...
85
votes
5answers
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?
70
votes
4answers
140k views
Getting coordinates from geometry in PostGIS?
This line:
SELECT geom FROM myLayer.geom
will return a hex value.
How would I get decimal values, which is a human-readable coordination?
65
votes
6answers
23k 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 ...
63
votes
2answers
12k 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
13k 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 ...
58
votes
1answer
46k 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?
...
54
votes
2answers
39k 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?
54
votes
2answers
108k 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','...
52
votes
3answers
12k 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 ...
51
votes
3answers
41k 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?
49
votes
4answers
34k 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
4answers
37k 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 ...
47
votes
7answers
11k 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 ...
46
votes
2answers
12k 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,...
43
votes
8answers
18k 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 ...
41
votes
3answers
17k 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.
41
votes
5answers
19k views
Calculating average width of polygon?
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
12k 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
21k 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 ...
39
votes
2answers
43k 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 ...
38
votes
4answers
24k 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 ( ...
38
votes
6answers
21k 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 ...
38
votes
5answers
25k 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 ...
37
votes
21answers
31k 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-...
37
votes
4answers
34k 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(
'{
...
37
votes
7answers
20k 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 ...
37
votes
4answers
32k 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 ...
37
votes
4answers
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, ...
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
5answers
36k 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
34k 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 ...
35
votes
2answers
15k 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....
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 ...
33
votes
1answer
30k 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 ...
33
votes
4answers
10k 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 ...
32
votes
2answers
30k 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 ...
31
votes
9answers
9k 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:
31
votes
2answers
48k 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 ...
31
votes
3answers
11k 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 ...
30
votes
6answers
30k 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)...
29
votes
4answers
6k 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 ...
29
votes
7answers
12k 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 ...
29
votes
4answers
18k 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, ...
28
votes
3answers
18k views
Postgis TopologyException: side location conflict at 226.001 39.5158, what's the problem?
after st_union() PgSQL returns a note:
NOTE: TopologyException: side location conflict at 226.001 39.5158
And in the results there are two rows with empty geometry and other rows are ok.
What ...
28
votes
6answers
12k 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
9answers
16k 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
4answers
58k views
Return all results within a 30km radius of a specific lat/long point?
I have a table with a column the_geom which contains data similar to:
...
27
votes
3answers
30k 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 ...
