Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,278
questions
113
votes
7answers
52k 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 ...
96
votes
4answers
33k 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?
78
votes
4answers
156k 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?
71
votes
7answers
27k 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 ...
67
votes
2answers
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 ...
66
votes
1answer
53k 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?
...
60
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 ...
57
votes
3answers
13k 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 ...
57
votes
2answers
116k 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','...
56
votes
2answers
45k 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
4answers
38k 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 ...
51
votes
3answers
45k 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?
48
votes
3answers
14k 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,...
47
votes
4answers
30k 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 ( ...
47
votes
4answers
42k 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
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 ...
46
votes
3answers
18k 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.
46
votes
9answers
21k 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 ...
43
votes
5answers
22k 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,...
43
votes
5answers
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 ...
43
votes
2answers
55k 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 ...
42
votes
5answers
30k 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 ...
40
votes
6answers
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 ...
40
votes
4answers
41k 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(
'{
...
40
votes
6answers
22k 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
3answers
52k 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 ...
39
votes
4answers
34k 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 ...
38
votes
21answers
33k 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-...
38
votes
10answers
11k 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:
38
votes
1answer
34k 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 ...
37
votes
5answers
37k 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 ...
37
votes
7answers
23k 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
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
Choosing coordinate system 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
3answers
38k 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
16k 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 ...
34
votes
2answers
33k 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 ...
34
votes
3answers
38k 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 ...
33
votes
4answers
11k 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
6answers
36k views
PostGIS Geometry type not matching column type in QGIS
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)...
32
votes
2answers
27k views
What is the unit used in ST_Distance()?
I wonder what the unit is of the float being returned from ST_Distance.
In the documentation it says:
...cartesian minimum distance (based on spatial ref) between two geometries in projected units....
32
votes
4answers
73k 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:
...
32
votes
3answers
13k 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 ...
31
votes
3answers
40k views
Creating geometry from lat/lon in table using PostGIS?
I have a table with over 800,000 records including a lat and long column. The data source did not create geometries, so I was looking around how to do this. I know there's ST_MakePoint(x,y,SRID) but ...
31
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 ...
31
votes
3answers
11k views
Poor performance with storing large rasters in PostGIS and visualising in QGIS
my question concerns the use and performance of several software tools in conjunction, namely PostgreSQL, PostGIS, QGIS, and GDAL.
I'm a long-time ArcGIS, Python, and R user who is interested in ...
30
votes
2answers
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 ...
30
votes
3answers
21k 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 ...