PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
57 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: ...
0
votes
1answer
48 views

How do I create and manage custom SQL functions in CartoDB?

I'm working through a CartoDB tutorial at the moment, and got to the point of creating a custom SQL function. Do I just write this in the "CUSTOM SQL QUERY" Field on the right of my table? How do ...
1
vote
1answer
53 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 ...
2
votes
1answer
102 views

How to use ST_DWithin to query features from one table within a distance of a specific feature or features in another table

I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific ...
2
votes
0answers
33 views

Efficient processing of ST_ValueCount Results in Postgresql

General Problem: I have a table of rasters. The rasters are classified MSI images, so each pixel is an integer indicating the class of the pixel. For a number of regions, I am querying pixel counts in ...
1
vote
1answer
42 views

Deleting records from table where ST_DIstance from another record in another table is greater than a certain amount

I'm currently learning SQL on the run, and have come across a situation where I'm trying to delete records from a table depending on a function result. I can select the records of interest with the ...
2
votes
1answer
44 views

How to find records using a distance filter, in a table of Points?

My table fields are: POSIC (id, geom, dt_inclusion). DB is PostGIS. Data comes from a GPS Source, then i can create a route ordening the points by Date_Inclusion. So, lets say i have registries A,B,C ...
0
votes
0answers
32 views

postgis ST_Intersect on 2 valid geometries throws TopologyException

Using Postgis 2.1 on PostgreSQL 9.3.4, I'm getting an error when I try to intersect two valid geometries: TopologyException: found non-noded intersection... These geometries are created on the fly ...
1
vote
2answers
58 views

How to aggregate pgr_dijkstra results by attribute?

I working with a table with a lot of points per road/street. I'm using pgRouting's pgr_dijkstra method to give me a path, but it returns too many points per road/street, I'd like to know if there is a ...
1
vote
2answers
50 views

PostgreSQL/PostGIS: Closest outside point (health center) to point (education center) inside polygon (danger zone)

I am trying to find the closest health centers (points) to the education centers (points) located within the "danger zone" (a specific polygon) while excluding those health centers located in the ...
1
vote
1answer
33 views

How can I count points from several buffer distances in PostGIS updating the table?

I have a problem regarding a calulation on PostGIS. I have to calculate how many facilities fall within a given distance. This is not really a problem though, but updating my table is. I have this ...
2
votes
2answers
113 views

PostGIS operations extremely slow when called in a script

OK, I am aware that this is rather vague, but unfortunately I was not able not reproduce my problem in simplified code. I am running a series of functions within another function (main()). All ...
2
votes
0answers
49 views

Dijkstra's Algorithm direction issue

I am trying to implement Dijkstra's algorithm, my query is working fine if I set direction parameter to false. I have to consider the direction of travel while calculating route from point A to point ...
0
votes
1answer
32 views

Restoring public.layer_styles table from PostGIS dump

I created a PostGIS database on a PostgreSQL server and stored some QGIS styles (in public.layer_styles). When dumping and restoring the database only this table gives me an error and stays empty. I ...
0
votes
1answer
50 views

How to add tiger schema to the database search path? [closed]

The tiger schema must be added to the database search path for the functions to work properly. I just started recently to use tiger dataset and postgres database and I have basic understanding of ...
6
votes
2answers
178 views

How to do a spatial join in PostGIS that returns on the minimum value of point intersecting polygon?

I have a polygon table (buffer) and a point table (dem_points), and I want to do a spatial join on them. I need to join the point with the minimum elevation (dn) to its spatially corresponding ...
1
vote
1answer
73 views

Can't upgrade to PostGIS 2.2

I am running on MAC OSX El Capitan. My version of postgres is 9.4.5.0. My PostGIS version is 2.1.7. I cannot upgrade to PostGIS 2.2, because when I am trying to do, it pops "PostGIS requires SFCGAL >= ...
2
votes
2answers
38 views

PostGIS - raster and bands

I have several images from different times and for every time I have three types of image (temperatureA, temperatureB, pressure). Should I store them as every time as band in a single raster with N ...
0
votes
1answer
48 views

TypeError: a.geometry.getBounds is not a function in OpenLayers.js (line 611, col 334)

I'm using OpenLayers 2.13 and Postgresql 9.3 for my development activities. I want to load region data from database and load into openlayers. Below result i'm getting from St_astext(geom) in ...
2
votes
1answer
291 views

How to enable SFCGAL in PostGIS?

I have the problem with the enabling extension SFCGAL. CREATE EXTENSION postgis_sfcgal; doesn't work for me even I have the PostGIS v2.2. When I do that, it throws an error ERROR: could not ...
0
votes
1answer
55 views

Generating pivot table in PostgreSQL that ties cities to counties using QGIS (or PostGIS)?

I have a cities shapefile and a counties shapefile. I have the counties shapefile imported to a PostgreSQL table. I haven't transferred my cities to PostgreSQL yet, but the issue I really want to ...
1
vote
1answer
51 views

How to get a list of all objects contained in my different QGis layers

I would like to know how we could do in QGis to get a list of object contained in my different opened layers: We have n .SHP files or PostgreSQL Views (they all contain at least an "object type" ...
1
vote
0answers
45 views

refine nearest_node with radius-distance ( ST_Distance, ST_DWithin )

i am a hobby-IT-person and currently stuck with some simple problem, maybe somebody can help me: This is my working POSTGIS-function, source by underdark: ALTER TABLE distance_graz ADD COLUMN ...
1
vote
1answer
63 views

Join two tables completely on geometry (postgis)

I have two tables with the same street network as geometry (but not a same id). What I want to do is to join both tables with all attributes to one for all geometries which overlap exactly. ...
2
votes
0answers
27 views

Creating lines from lonely nodes in a linestring table to closest point on another linestring table

I want to segmentate a country (denmmark) by using barriers such as larger roads, railroads and the border/ coastline as the delimiting lines of the polygonise function in QGIS. But as most roads and ...
2
votes
0answers
39 views

Changing border style in OpenStreetMap?

I am trying to figure out how to change OpenStreetMap country borders color/thickness. I've set up a tile server using OSM data, PostgreSQL & Mapnik. The default mapnik style is used. There ...
3
votes
2answers
212 views

Is there a way to change ST_asGeoJSON return values?

The function ST_asGeoJSON returns something like: "st_asgeojson": { "type":"MultiLineString", "coordinates":[ [ [-3.13512629299993,39.7174353130001], ...
3
votes
2answers
81 views

PostGIS does not find intersection between line and polygon

I have a line and polygon: I would like to programmatically figure out whether they intersect or not. So, I do: select ST_Intersects ( ( select ...
0
votes
0answers
30 views

Number of rows returned via `shp2pgsql` for a given shapefile

With the shp2pgsql tool, I see it converts a shapefile into a SQL statement. Upon closer examination, does the SQL generated ALWAYS contains 1 insert statement for 1 row? Or are there instances where ...
1
vote
1answer
28 views

counting values in an update statement postgres [closed]

I have a repetitive method I want to execute and I expected underneath script to work. I found out the count() function is not allowed in postgres update statements. What is the most logical and ...
2
votes
2answers
118 views

QGIS Web Client - postgreSQL Layers wouldn't shown

since updating my QGIS to version 2.8.3 the postgreSQL Layers wouldn't shown in the QGIS web client. In the Desktop version the project works very well I only can see the projecttitle and the groups ...
1
vote
1answer
54 views

OpenStreetMaps: what are the !pixel_width! and !pixel_height! in the queries

The Github repository containing the CartoCSS styling for the OpenStreetMaps dataset also defines queries for the different layers (see this file). An example of such a query is SELECT way, ...
0
votes
2answers
41 views

Finding vertex in comon with most polygons in PostGIS?

I have a table with polygon geometry, in this case representing counties. I'm trying to figure out how to write a query that returns vertices sorted by the number of polygons that share that vertex. ...
0
votes
0answers
36 views

Receiving -infinity in geoserver wfs GetFeature result

I'm currently using: - Geoserver 2.7.2 - PostgreSQL 9.4.4 - PostGIS 2.1.9 When I submit the following wfs GetFeature request: ...
1
vote
0answers
26 views

PCA on correlation matrix in PostgreSQL

Does anyone know how to perform PCA using PL/pgSQL in a PostgreSQL function, considering correlation matrixes of potentially high order? Maybe a specific library for this problem... Thanks.
1
vote
1answer
44 views

Applying OSM z-order after osm2pgsql with qgis

I've trying to find a solution to applying the OSM z-order in qgis after using osm2pgsql. Everything is working perfectly, until I add the each layer to the canvas, where the z-order is not being ...
0
votes
0answers
44 views

Fulltext Search with Spatial Search with pagination

We tried to use postgresql+postgis to save our spatial data with points/lines/polygons. Though the postgresql have a built-in full-text search engine, we have to use lucene for some reasons, and ...
1
vote
0answers
51 views

Gisgraphy importer crashing, possibly due to JDBC connections problem. Is there a fix? [closed]

I've been trying to install gisgraphy for the past few days. The setup has went well, but the software crashes while importing data. I'm only importing data for a single country so it's not overly ...
1
vote
0answers
49 views

raster2pgsql stops at line 53 at RETURN

I am importing some very large rasters (in the tif format) into Postgres SQL (9.3) using raster2pgsql on the command line. Most of the rasters imported fine, some had to be converted to the img format ...
1
vote
1answer
28 views

PostgreSQL - PostGIS linkup error

I have a database that I viewed in QGIS using PostGIS. I managed to link them up using the correst host, database combination last year. I am now revisiting the work and am unable to link them ...
0
votes
0answers
36 views

pgr_dijkstra doesn't return when cost values are low

I'm using a different cost column in pgRouting, and passing it to pgr_dijkstra but it returns 0 rows. I analyzed that column and contains some 0 and values in scientific notation (1e-6) and also ...
3
votes
1answer
148 views

Postgres, loading raster into db and visualize with qgis

I'm trying to import a TIF raster into a Postgres db using this command: Raster2pgsql -s 26591 -I -C -e -Y -F –N -M C:\Users\Ivn\Desktop\Postgre_HOME\scratchmres1.tif -F | psql -U postgres -d ivndb ...
2
votes
1answer
56 views

Generating Shapefiles from PostGIS as a Plugin

I used to generate shapefile from PostgreSQL database that is installed with PostGIS tables as templates (geometry_columns and spatial_ref_sys) Shapefiles generated just fine using the following ...
0
votes
1answer
36 views

Using ST_Extrude in Postgres 9.2, with PostGIS 2.2.0

I'm trying to use the function ST_Extrude() in Postgres 9.2. I enabled the PostGIS 2.2.0 extension and the PostGIS_SFCGAL extension (which carries the ST_Extrude function). I can see the function in ...
1
vote
1answer
37 views

Keep empty GRID Cells in PostgreSQL (after join)

I want to join a 100*100m Grid with a point layer. The points contain the number of people living in the grid cell. There are some cells with no polulation, which I want to keep with the Attribute ...
2
votes
1answer
122 views

Installing Postgis 2.2.0 on Windows?

I'm trying to get the newest version of Postgis (2.2.0) up and running with Postgres 9.4. I'm using Windows and the only installation manuels I found, are Linux-only. Can anybody point me in the ...
1
vote
2answers
77 views

Edit Geometry with digitizing tools in QGIS of a postgis layer

I have a multiline PostGIS layer added to my QGIS 2.10.1 Pisa. I'm able to add features using the digitizing tools, but I'm not able to use the delete or node tool. It remains gray even if I'm in ...
0
votes
1answer
41 views

Error in using 'psql' from osm2po [closed]

I am following this direction to make network dataset from OSM data to use ArcGIS network analysis (by using osm2po and psql)(http://revenant.ca/www/postgis/workshop/loadingdata.html). So far so good ...
2
votes
1answer
17 views

Multi-string data-set without z layer, applying ordinal values as a workaround

I'm new to GIS and have been learning a lot over the last few weeks, however, I've finally come to a point where I could use some help. I'm working with the pdx_streets data-set from ...
0
votes
1answer
154 views

Install Postgresql / Postgis with SFCGAL

I need to access ST_StraightSkeleton() function. At the moment i have a Postgresql instalation PostgreSQL 9.3.4 compiled by Visual C++ build 1600, 64-bit installed via EDB with Postgis 2.1.7 on a ...