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

learn more… | top users | synonyms

4
votes
1answer
72 views

What is XYR in QGIS?

I am familiar with XY, XYZ, and XYZM, features but I created a spatial layer in PostgreSQL and added it to my QGIS. When I opened the Vertex Editor it showed me values for XYR and I am not familiar ...
1
vote
1answer
25 views

postgis create a new layer from the st_within query for points with another layer

I am trying to create a layer that contains all of the businesses in Los Angeles proper. I have a shapefile for the polyon of the LA boundaries (shp_laboundaries) and I have a set of points in the la ...
1
vote
0answers
29 views

Import raster using raster2pgsql with out-of-db option not working

I am using raster2pgsql to import DTED data into postgis database. I would like the data to be out-of-db, I am using the command as follows. raster2pgsql -R /home/gis/elevation/DTED/area.dt2 ...
2
votes
1answer
79 views

Raster statistics for polygon areas PostGIS

I'm trying to get zonal raster statistics for each polygon area. I have used the following method from this site: SELECT bufid, (ST_SummaryStatsAgg(ST_Clip(rast, geom, true))).* FROM rasttable, ...
2
votes
0answers
47 views

How can I convert a shapefile from a single line segment to separate side segments using PostGIS?

I have a shapefile (STC) which is a single line segment per street with information for both the odd and even sides of the street. This .shp file has to be converted to separate side segments. To do ...
0
votes
0answers
41 views

Unable to install pgrouting for postgres 9.3.5/postgis 2.1.7

I am running postgres 9.3.5 and postgis 2.1.7 on Ubuntu Server 14.10. I am trying to install pgrouting, but having no luck. 1) I tried the following based in the official workshop documentation: ...
3
votes
1answer
105 views

How to handle large amounts of points

I'm using Postgres 9.4.4 with Postgis 2.2.1., my issues is that i have about 3 million of points i want to fetch and display on a map with leaflet. The problem is that those are too many points, what ...
3
votes
0answers
70 views

Import Large Shapefile to Postgres

I'm trying to import a large dataset to Postgres, of about 600MB, that sits on a remote AWS server. But I get the following error Not enough memory to allocate requested memory (nRecords=26923892). ...
1
vote
1answer
59 views

GeoDjango - save two Point fields, one for geography and one for geometry

Most of the explanations I've read for geography and geometry seem to suggest that one must only use either geometry or geography, and that the only way to use both is to cast/convert between one and ...
3
votes
3answers
695 views

Why is geography faster than geometry for distance calculations?

Intuitively, I thought geometry would be faster than geography distance calculations, but this says otherwise: ...
1
vote
1answer
56 views

Syntax error at or near “1” — PostGIS in Action — Chapter 11 SQL data dump

I'm following along with PostGIS in Action, 2nd ed., chapter 11, while attempting the SQL dump from the data files and I'm getting the following error: ERROR: syntax error at or near "1" LINE 189: 1 ...
1
vote
1answer
31 views

Load postgis database with PK from two fields in QGIS [closed]

I have a PostGIS database having a Primary Key from two fields id_cat and id_cdad. When I load this database in QGIS in my computer under Linux there are no problem. I unmark "Select ID" and I can ...
0
votes
1answer
64 views

how to debug mapserver / postgis returning 0 results? ( MapServer WMS 1.1.1 / Google Earth / QGis )

user30184 answered the same question here Why is mapserver returning a blank image? user30184 answers to start by specifying SRID and that it often has to do with extent and projection. I imported a ...
3
votes
2answers
74 views

Does Oracle Spatial calculate geodetic distance?

I've got to migrate a PostGIS database to Oracle Spatial, and am stuck on a problem: In a view, I have a column that amounts to ST_Distance(geography(geom_1), geography(geom_2)), effectively ...
2
votes
2answers
52 views

PostGIS Address model/schema industry standard?

Is there an industry standard or best practices for a database of Addresses? I've searched for examples of Address models, and all I've found are very barbaric models that have something like address ...
3
votes
1answer
87 views

How to cast geometry to varbinary in PostGIS / PostgreSQL

In MSSQL Server, casting geometry to varbinary(max) produces a string that looks like this: 0x3D0B0000010CA0F3BE3B5CE9474100EC414E3AF93941 When you insert that string into a geometry column, a ...
4
votes
2answers
126 views

How to display vertex markers in QGIS for non-editable layers?

I've a PostGIS layer with read-only privileges. Is there any way to display the vertex markers of the layer?
2
votes
1answer
47 views

Load filtered OSM relations into Postgis with OSMOSIS

I am really hitting the wall trying to grips with merging filters in OSMOSIS. I would like to load relations, their related nodes and ways. If I use a tag filter like this: --tf accept-relations ...
0
votes
1answer
80 views

How to connect PostGIS raster data to Geoserver?

The data I have used is DEM. It creates a table and has 2 columns in PostGIS. Is it necessary to create a new table again and follow the configuration process as mentioned in the Image Mosaic JDBC ...
2
votes
1answer
146 views

Convert a raster to GeoJSON polygons in PostGIS

I'm trying to convert a raster to a polygon and export it from PostGIS as GeoJSON. Specifically I want each pixel to be a polygon with associated the raster value. PostGIS seems to have a few ...
3
votes
0answers
28 views

Postgis - Construct linestring of length N

Give a table of linestrings (roads) ogc_fid, wkb_geometry And a road with ogc_fid = X In postgis how could I construct a linestring that is of length Y metres made up of roads starting from road ...
3
votes
2answers
157 views

Find points that are unique between two tables

I have been trying to work out a query that returns the geometry of points that are unique (eg. not supliacte). Is there a simple way to perform it in just one query? geom is the only common ...
3
votes
0answers
32 views

How to get the geometries based on column values in postgis?

I have the following table in postgis I want to group the species along with geometries based on column values of 'val' i.e 11,11,11,11,11,11,11-corresponding species 6,6-corresponding ...
3
votes
2answers
53 views

What is the SRID of LCC projection?

I have a raster file with LCC projection and a point dataset with SRID 4326. I want to extract raster value to point. When I use the query SELECT ST_Value(rast, geom) val FROM testraster5, ...
0
votes
0answers
49 views

populating a geojson from postgresql with php depending on zoom level

i am about to load a few thousand polygons (a grid 500x500m) from a postgresql db into a leaflet map with php script converting the data into a geojson. the php script populates the geojson depending ...
1
vote
1answer
31 views

ST_BestSRID function in PostGIS

This talk by Paul Ramsey mentions that there is a _ST_BestSRID function in PostGIS. I assume it exists to find the best projection when casting unprojected data for other uses. I cannot find docs on ...
1
vote
0answers
34 views

ST_Intersects in postgis

My version : postgres 8.4.1/postgis 1.4.0 I want to perform a simple spatial join with the following query : SELECT code_insee, nom, id_canton, lib_canton FROM localisants.alca_toponyme_commune ...
1
vote
0answers
64 views

QGIS - Alternatives to Offline Editing Plugin

I am looking for a way to edit my postgis layers offline (in field) using a Microsft Surface Tablet. I have been testing the "Offline Editing Plugin" unsuccessfully. These are the issues I have ...
1
vote
0answers
27 views

St_intersects returning false results in postgis

I want to intersect point geometry with raster. In postgis I'm executing select st_intersects(geom,rast) from testraster1,pft_pft and the result is false even though the points are sitting on ...
1
vote
1answer
70 views

Finding and merging lines that touch in PostGIS?

I would like to select and merge segments that touch like in the image below. My table contains single segments with their own id (shown in the figure), I just want to isolate single "group" of ...
1
vote
1answer
128 views

How to install pl/r pl/python pl/v8 in Ubuntu?

I am trying to find instructions on installing the PostGIS Procedural Languages (PL) for R, Python and V8 as required for the "PostGIS in Action" 2ed. book. I searched on google and couldn't find ...
0
votes
1answer
30 views

What does skew X/Y do in PostGIS?

I am just wondering what the exact function of skewX and skewY in raster constructors is. According to the documentation linked: ST_MakeEmptyRaster — Returns an empty raster (having no bands) of ...
3
votes
2answers
149 views

How to interpret a WKB (Well-known-binary)?

I read this example of a WKB (well know binary) from "PostGIS in Action" 2ed.: SELECT ST_AsBinary( ST_GeomFromWKB( ...
4
votes
0answers
47 views

Why won't my points (that I created by intersection) won't intersect with the geometry they've been created from?

I created a set of points by intersecting a linestring layer with the boundary of a multipolygon layer like this: CREATE TABLE freiraumkataster_grenze AS SELECT gid, ...
1
vote
1answer
59 views

Why is raster data in range 0-255 in PostGIS?

I'm having trouble interpreting some data I've loaded into a PostGIS database. I scraped rasters from here and converted to sql files using raster2pgsql. I'm now using PostGIS's special functions to ...
5
votes
2answers
125 views

How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting. CREATE TABLE schema.poly_a ( gid ...
1
vote
0answers
39 views

QGIS crashes when using PostgreSQL/PostGIS triggers

When using PostgreSQL/PostGIS triggers saving layer edits gets slower and slower every time when saving a layer edit. This issue occurs after a few up to a few dozen saved layer edits. Finally QGIS ...
1
vote
1answer
115 views

How to check the encoding in the .dbf files?

I am having difficulties when I try to load a shapefile into my database, the is because I do not know the encoding of the dbf portion of the shapefile. The attribute tables are in Spanish, I have ...
1
vote
1answer
50 views

Can't create PNG file with “st_png” function PostGIS and Java

I have this code in Java: conn = DriverManager.getConnection ("jdbc: postgresql: // localhost: 5432 / postgis_23_sample" "postgres", "admin"); conn.setAutoCommit (false); String s = "SELECT ...
6
votes
2answers
160 views

How can I cluster all points in a postgis table together based on their distance from each other?

I have a PostGIS (in postgres 9.3) table which has Points. I want to figure out the "clusters" in the data. If 2 points are < X distance apart, they should be added to a cluster, and this should be ...
1
vote
1answer
30 views

PostGIS update statement returns wrong information

I have two tables in postgis with polygon data. Both are in wgs84. I want to update a column (column1) in the one table (table1) with information from a column (column2) in the other table (table2), ...
1
vote
1answer
48 views

Performing raster calculations with more than two bands in PostGIS?

We need to perform raster calculations using more than two bands in PostGIS. At the moment, ST_MapAlgebra has a two-band form but doesn't support more than that. I'm open to any solution others ...
0
votes
0answers
60 views

How to “INSERT” geotiff raster into postgresql/postgis table using psycopg2

Can someone walk me through how to insert a raster into a prebuilt postgresql table? The table has the postgis extension and I've been able to manually insert a geotiff using raster2pgsql, but I would ...
2
votes
1answer
109 views

GeoDjango, distance filter based on database column

I have a geodjango model with fields coverage = 10 * 1609.344 reference_point = Point(-72,45) #or some other lat lng class ServiceArea (geo_models.Model): center = ...
2
votes
1answer
38 views

Select a subset of a PostGis table

I'm using a big table imported by a *.shp file of the city of San Diego. When I run pgr_dijkstra it takes more or less 5000ms, even for small distances. I was thinking, in order to improve ...
1
vote
0answers
30 views

PostGIS: Find nearest point given coordinates from another table

So I have two tables, one table is called stores which has the name of the store as well as the geom column that contains the coordinates for the stores. The other one is customers which has the ...
4
votes
0answers
33 views

Wrong coordinates after loading OSM to postgre

After loading OSM data for whole Europe to postgres (Osm2pgsql) I'm trying to add them to Geoserver, but the coordinates are totally wrong. The SRS is correct (4326), but the coordinate values are ...
1
vote
1answer
31 views

Adding more information to a PostGIS table via QGIS [duplicate]

I have a table within PostgreSQL/PostGIS that contains all point data. I need to add another large dataset to this existing table without overwriting the table. When I go into DB Manager within QGIS ...
2
votes
1answer
74 views

Visualize PostGIS 3D line geometry [closed]

I have PostGIS table with 2 geometries ( "GEOMETRY(MULTILINESTRINGZ)" and "GEOMETRY(LINESTRINGZ)" ). There are lines made just from 2 points - XY are WGS and both of them have random elevation Z (I ...
1
vote
1answer
42 views

Given a lat/long how to find the nearest node in a route calculated by pgr_dijkstra?

I'm creating a table route_<route_id> for the route given by pgr_dijkstra, like this. In my application, I have a websocket with a mobile client that it's sending it's current position every x ...