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

learn more… | top users | synonyms

0
votes
1answer
61 views

postgis/postgres ST_Distance and GROUP BY

Trying to find the nearest road to a postcode point and order the results by distance. The roads are named according to UK convention so A606 etc and there are multiple entries for many road numbers ...
1
vote
1answer
26 views

Finding zero-length LINESTRINGs the wrong way gives funny answer

I tried select st_astext(geom) from table where st_pointn(geom, 1) = st_pointn(geom, 2) But I get some results which don't match the criteria. For example: LINESTRING(-117.1642086 ...
1
vote
1answer
41 views

PostGIS Raster performances

I ingested two Landsat images into PostGIS database using : raster2pgsql -I -C -s 28356 -M -t 100x100 LS7_20150425_B40.tif public.red | psql -d postgres -U postgres raster2pgsql -I -C -s 28356 -M -t ...
1
vote
0answers
24 views

Best way to store, elaborate and query raster data [closed]

I have created multiple raster images with GRASS-GIS (~150Gb), that I'll need to multiply for a certain coefficient, and query the results that need to by saved just temporally. The portions of the ...
2
votes
1answer
48 views

pgRouting: weight routes with similar attributes higher

I am creating my own routable network using pgRouting. I would like my shortest path queries to prefer a route made up of the same attribute. For example, in the image below (please excuse my ...
1
vote
1answer
51 views

Using raster2pgsql to display data in QGIS

I am quite new to this process (using raster data in PostGIS) and tried to find some tips/solutions on other threads but I didn't manage to get things working. I ingested some Landsat data into my ...
1
vote
0answers
29 views

How to translate QgsMapCanvas coordinates to wgs-84?

My application uses QgsMapCanvas. I've got point coordinates in wgs-84 crs and i need to add this point to vector layer (represents postgis table) inside of the canvas. Have no idea how to ...
1
vote
2answers
37 views

Viewing results of ST_Difference

I've got an ST_Difference query runnning error free in PostGIS, and in PGAdmin I can see the output table but it's not viewable in QGIS - The table name appears in DB Manager but says "There is no ...
3
votes
1answer
48 views

How to unit test distance calculation beetween to points?

I want to write unit test for this query select a.id, b.id from channel a, channel b where ST_Distance_Spheroid( a.coordinates, b.coordinates, 'SPHEROID["WGS 84",6378137,298.257223563]' ) ...
0
votes
0answers
41 views

Filtering out of bounds' coordinates

I have a set of coordinates, that I receive from GPS. I store them in database, its geometry is 4326. I am using the next query to calculate the travelled distance: ...
0
votes
0answers
54 views

FME autoincrement id is not recognized

I have made a workflow in which I pass some data from a shape file to a postgis database using FME 2015. The table in the database has several columns and one of them is an id with an autoincrement ...
2
votes
1answer
45 views

Group by Location within N Meters in PostGis

In PostGIS, given several Location objects, each of which looks something like this: { :id => 1, :latitude => 32.4196, :ll_point => "(-10111.1877176122, -5384086.44077608, ...
1
vote
1answer
53 views

How to add time and elevation dimension attributes to Geoserver rasters?

In geoserver, when I edit a postgis raster layer and click on the Dimensions Tab, I can't enable either of the Time or Elevation dimensions. The following message appears next to each of the ...
1
vote
0answers
40 views

Updating an Openstreetmap PostgreSQL database after the system restore

My Openstreetmap PostgreSQL database takes a long time – about 25 hours by now – for updating via Osmosis after I had made a system restore at the last weekend. Before making the system restore it ...
1
vote
0answers
34 views

How do I translate gps sports data into a representative plane

I'm hoping to use qgis and postgis for a project but I'm unsure of, how to not only frame the question but accomplish the task. If I have gps data from 1 runner on an oval track but who has run on ...
0
votes
1answer
37 views

Relate two tables based on geom

I have a problem, which is, I have a table ways (like in the Workshop of pgRouting) and I also have a different table street_name, where it contains the name of the street (among other information). ...
3
votes
1answer
59 views

PostGIS Polygons to single sp object

I would like to be able to take my SQL queries for PostGIS and make each row for the geom attribute part of an sp object so I can plot it with R's leaflet bindings. I can't really share what my ...
1
vote
0answers
31 views

Find census block for street intersection with tiger geocoder

I'm geocoding lots of addresses and map them to census block. Some of addresses are street intersections, like this select ST_X((geo).geomout)::numeric(8,6), ST_Y((geo).geomout)::numeric(8,6) ...
3
votes
4answers
65 views

Showing spatial coincidence using QGIS / PostGIS?

I have a database of fire hydrants (HYD). The positions aren't very exact, the other fields are very useful. I also have a database of GPS'd fire hydrants (GPS). We are manually moving the HYD points ...
3
votes
1answer
98 views

Creating centerline of polygon using PostGIS?

I am aware of Finding centrelines from polygons in QGIS? but I am looking for another Open Source GIS way to do this. Is there a possibility to create centerlines in PostGIS? I have this shapefile ...
1
vote
1answer
26 views

Render a large set of features in the map and make them interactive

We have a lot of different features (points,lines,polygons) saved in the PostgreSQL with PostGIS. And we have to display these data in a map (we use OpenLayers 2.13 at the moment), and the different ...
1
vote
2answers
43 views

Is there documentation anyplace for adding weather to PostGIS/Geoserver?

We have an internal PostGIS/Geoserver for rendering map tiles for the world based on OpenStreetMaps. We would like to overlay weather, but would like to do so without referencing external URLs. The ...
0
votes
3answers
73 views

Pass GeoJSON from .NET to Javascript

My .NET/OpenLayers/GeoServer/PostGres-PostGIS application allows users to create custom queries to get data from my PostGIS tables. The queries are called in .NET (not javascript) using a web method ...
1
vote
1answer
69 views

PostGIS Query To OpenLayers

I am trying to get data from a PostGIS datatable using .NET, pass it to javascript, create a layer in OpenLayers and add the PostGIS data to the layer. I can successfully query from the ...
1
vote
0answers
50 views

How to draw a raster over the World using Postgis

Im currently working on a system to track user interaction on mapwidgets. Now i need to visualize the bounds that users are looking at on the map. Since were using a whole lot of data i need to find a ...
1
vote
1answer
34 views

Leaflet CRS and PostGis

From the Leaflet.js documentation it states that it uses a default CRS of 3857. This implies that if I am going to generate points with PostGIS based on polygons, or points created by drawing or ...
1
vote
1answer
45 views

can geoserver select one of many rasters from a postgis table

I have a postgis table with many different rasters in it. Is it possible for geoserver to grab only one of the rasters from the table based on something like a where clause?
1
vote
1answer
29 views

Multipolygon postgis layer exposed via geoserver is empty at certain zoom levels

I have the following issue: There is a postgis table that contains a geometry column of type multipolygon (epsg is 4326, if it's relevant). This layer is exposed via geoserver. When I preview this ...
3
votes
0answers
85 views

Dissolving polygons in a large dataset

I need to dissolve polygons by the ID attribute in a large dataset (+-1gb shapefile). I have been struggling to find an efficient method to do this. I have tried the following methods: Postgis ...
1
vote
1answer
31 views

Normalize address query not working in postGIS

Okay, so I'm new at SQL, so bear with me. I'm trying to use the tiger extensions on postGIS (2.1.7) to normalize my addresses and then geocode them. The normalize query runs, but never returns an ...
2
votes
2answers
41 views

Distance between sequential points postgre

My table is as follows 1;"2015-10-02";"POINT(lat,lon) as geometry" 2;"2015-10-03";"POINT(lat,lon) as geometry" 3;"2015-10-04";"POINT(lat,lon) as geometry" How can I find the distance between two ...
1
vote
1answer
37 views

Multiple queries in pgrouting (length as result)

I am facing the following problem with two tables: The first table contains all my streetnetwork with cost, source, target. Another table contains 60 nodes (from the above network). Between all ...
1
vote
1answer
36 views

Using geomanager to query

I'm doing a project using the combination of GeoDjango & PostGIS & Openlayer so far, I've imported OSM data with osm2pqsql, done settings and I knew the data is constructed by ...
2
votes
3answers
75 views

How to split a polygon by a polygon using PostGIS?

I've a large polygon (table_a) that contains a smaller polygon (table_b). Now I want to split the larger polygon by the smaller polygon. Unfortunately st_split(table_a.geom, table_b.geom) isn't ...
0
votes
0answers
48 views

Importing array directly as raster to PostGIS in memory?

Below is python code I wrote to import downloaded rasters into a postgis table using raster2pgsql. I have a question involving efficiency. Instead of writing the raster to disk after doing array ...
2
votes
2answers
114 views

Reverse geocoding using OSM data imported into PostGIS

I'm trying to reverse geocode some points using an OSM import into PostGIS. I imported the OSM data (for Denmark so all addresses should be avaialable) into PostGIS using osm2pgsql enabling ...
1
vote
1answer
33 views

Is there any site where I can found city shapes in postgreSQL polygon format

Is there any site where I can found city polygon area (for postgreSQL or similar)? I don't know if such resources exists, and I don't know how to find it.
1
vote
1answer
17 views

postgis: union of faces of st_dump

I am trying to make a web application that renders 3D buildings (LoD1). I have a PostGIS (2.2.1dev) database which contains the 2D geometry of the buildings and the height. I used st_extrude to make a ...
3
votes
0answers
35 views

Trilateration with error account or lack of precision

I am developing a node.js/javascript application where I would have dozens of beacons in a row (positions known), which gives me their distances to specific object (3 or 4 distances per object). The ...
1
vote
0answers
37 views

Creating look up tables in QGIS that last more than one session?

It seems that when I create a Look up Table or Value Map for a shapefile or a PostgreSQL table in QGIS it saves it for one session, then when I open it again it had disappears. Can someone ...
0
votes
1answer
40 views

postgresql out of memory [closed]

I clip 70 raster with polygon in postgresql, the result are: out of memory, Fatal on request size of 10872 I read some solution about changing the postgresql.conf parameter but nothing work for me ...
2
votes
1answer
41 views

PostGIS - Polygons lying on the equator

Why are otherwise valid geography polygons (st_isvalid(col::geometry) is true) invalid when trying to calculate the area, when they lie on the equator? For example: select ...
1
vote
1answer
54 views

How does ST_Area in PostGIS work?

I am performing a simple calculation on a polygon known to be approximately 6226 km^2 in area. It is stored in a Geography (WGS84 SRID) column. The query is: select st_astext(col), st_area(col) area ...
0
votes
1answer
73 views

Clipping raster in PostgreSQL?

I want to clip raster (SRID 32649) with polygon(SRID 4326) into SRID 32648 First I transform raster using query create view ras68 as select st_transform(rast,32648) as rast from ras69 Then cutting ...
1
vote
2answers
73 views

Did the code example of geocode() in PostGIS tiger geocoder geocode all records then limit later intentionally?

The geocode function in PostGIS tiger geocoder extension have an example of batch geocoding, which seemed to be used by many people. However I'm not sure if the behavior of this example I observed is ...
1
vote
0answers
25 views

pgr_nodeNetwork doesn't place new nodes properly

I wanted to use pgr_nodeNetwork tool to split lines where there's no vertex on intersection. The tool doesn't seem to work as expected, though. It doesn't place new nodes right where the two lines ...
1
vote
1answer
56 views

PostgreSQL is refusing connections from MapServer

I have MapServer 6.04 running in Centos 6.7. I am able to server shapefiles and GeoJSON, but when I try to connect to a PostGIS (2.1.8) enabled PostgreSQL (9.4.5) database it fails with the error: ...
2
votes
1answer
52 views

Invalid GeoJSON when inserting data to a CartoDB (PostGIS) table from Leaflet

I've been following along to this tutorial from CartoDB. I create the GeoJSON using: var drawing = "'"+JSON.stringify(layer.toGeoJSON())+"'"; and then inserting using a custom SQL function ...
1
vote
1answer
51 views

Inserting spatial data into existing postgis geometry column from shape file based on ID

I have an existing postgresql database table "city_data" with Non-spatial data in the following format: City_ID as varchar, City_name as var_char, pop as int,.... I have enabled the postgis ...
0
votes
0answers
26 views

WFS to PostGIS with Geokettle

I am trying to use Geokettle to write records to PostGIS. Geokettle has has a WFS input step. I cannot get it to work, I cannot find much documentation about it and I wonder if anyone here has used ...