PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
0answers
5 views

PostGIS - raster - bit operations

I have a raster data in the database in separate rows. I would like to select 2 rows and do a bit operation with them (like |, &) and store the result in memory as a query output. Is this possible?...
0
votes
1answer
16 views

PostGIS Shapefile and DBF Loader - automatically Linked dbf file

This is my system: Windows 7 Postgresql 9.5 PGADMIN 3 PostGIS Shapefile and DBF Loader plugin I have imported data from a DBF file correctly with the plugin. My problem: This DBF file changes ...
4
votes
1answer
59 views

How to include aggregate field values of clustered points

I am creating a map that has point features representing things like campgrounds, picnic areas, boat launches, etc. In cases where many points are clustered in an area (representing amenities for the ...
0
votes
0answers
5 views

osm2pgsql: bad result during COPY error

Hi Everyone so I am trying to import only the roads dataset into postgres. I have taken osm data from geofabrik regions and extracted roads from them using osmfilter them then combined them using ...
0
votes
1answer
30 views

How to calculate the start and end lat/lon of a linestring which is not a part of a ST_buffer?

I'm trying to find out the lat/lon of points on a linestring that are not contained inside a geometry formed by a ST_buffer. I can find if the linestring intersects the geometry but I'm not able to ...
2
votes
1answer
265 views

Is there any readily available implementation of Mapserver (or Geoserver)- Postgres-Openlayers combination?

I am developing an web GIS for map data presentation for public. Is there any readily available implementation of Mapserver (or Geoserver)- Postgres-Openlayers combination or similar? More Details: ...
1
vote
1answer
695 views

PG::UndefinedFunction: ERROR: operator does not exist: point = unknown

Recently, I developed a feature to save and fetch points(lat and lon) from DB. It is working fine on my local, but on production database it is giving error: PG::UndefinedFunction: ERROR: operator ...
1
vote
1answer
23 views

inserting points in postgis with pdo (php)

I have a problem with sql insert statement using PDO (php, postgis, postgres). I have tried to use different combinations, but none of them work. Here is what I ve got: $latitude= 46.07175; $...
0
votes
1answer
70 views

PostGIS - Calculate of the average distance between points for a delimited area

I am working with PostGreSQL I have 2 data sets: 1) A points layer where each point represents a building. 2) A Polygons layer created from the points layer using convexhull and clusterwithin ...
0
votes
1answer
24 views

How to calculate nearest point to a linestring in PostGIS?

I'm following this question to calculate POI nearest a road(linestring). I'm able to calculate nearest points in the linestring but I'm not able to find the distance from the POI to nearest point(...
1
vote
1answer
70 views

Creating polygons (squares) from point using PostGIS?

I want to create a regular grid of polygons/squares from this point knowing that between two successive points 100 meters, so each square would have 100*100 m2 in its area.
1
vote
1answer
99 views

Installing Aggregate Polygons Function in PostgreSQL 9.3

I'm new to PostgreSQL and I've been trying to run a sql function written by Dr. Horst Duester, found in http://www.kappasys.ch/cms/index.php?id=65&L=5, which is used to aggregate detached polygons....
0
votes
0answers
17 views

conditional rule on trigger to historical table [closed]

I have a history table that tracks modifications to attributes/geometry for each layer I need to set a condition that if the attribute is 'Y' for the column "export" in the "span1" layer, the trigger ...
3
votes
2answers
68 views

ERROR: Only lon/lat coordinate systems are supported in geography

I'm trying to find out all the circles stored in the database(The circles are present all over the world) that intersect or overlap a given circle. I've stored the circles like this: CREATE TABLE ...
-1
votes
0answers
29 views

Error in using ST_Distance in plpgsql function [closed]

I want to calculate distance from address points to all streets within a distance of 50 meters using plpgsql. I have tried to write following function: Create or Replace Function get_dist(ad geometry,...
1
vote
0answers
29 views

How to visualize PostGIS data(geography) in QGIS?

I've created a table that stores spherical circles located all over the globe. But I'm not able to view this data in QGIS. This is my table: CREATE TABLE circles(gid serial PRIMARY KEY, the_geog ...
6
votes
1answer
234 views

Replacing corporate GIS? [closed]

I'm looking at replacing our corporate GIS, this is the first time that I have had the responsibility of such a task despite my years of experience with the software as an end user. We will be ...
1
vote
1answer
60 views

How to use the Tile Server to fetch latitude/longitude data

I have installed a tile server using the following link and my main purpose was to geocode as many times as possible. Initially I was using openstreet API to geocode but they have certain limits so ...
2
votes
1answer
147 views

How to calculate width and lengths of street layer in Postgis/PostgreSQL?

I was able to project the point location (ad.shp) to the nearest street (line.shp) like this. My approach was like this. I used ST_ClosestPoint() to iterate this for every point location within a ...
0
votes
0answers
20 views

line_locate_point: 1st arg isnt a line

i used this query for routing procedure SELECT pgr_nodeNetwork('edges', .006); I am following this workshop http://workshops.boundlessgeo.com/tutorial-routing/ and this is the error, I can't ...
0
votes
0answers
35 views

Creating a table and inserting selected streets using plpgsql functions

I am trying to select all streets within 50 meters distance from each address location and store them in a separate table using Postgresql (plpgsql) functions. So far, I have written the following two ...
4
votes
1answer
241 views

Error installing PGRouting database extensions

Im trying to install pgRouting and I cannot reach the end of the way because of the following error: psql:/home/user/share/postgresql/contrib/pgrouting-2.0/pgrouting.sql:2459: ERROR: could not load ...
2
votes
2answers
45 views

PostGIS: ST_DWithin: Why is the output empy?

I have just one table of street segments of Berlin (OSM Data, imported using osm2po, SRID set to 25833). I want my query to output all street segments, that are within a certain distance (e.g. 2000m) ...
1
vote
2answers
39 views

Select transposed values

I am trying to do a conflation. This is my method, and my problem: METHOD Step 1: I have two tables with road geometries. I am using ST_Dwithin to subselect geometries from table 'A' that lie within ...
2
votes
1answer
35 views

Is there a way to automatically displace colliding labels?

I have a PostgreSQL table, which i'm loading on QGIS Essen, that relates many rows to the same object and all this rows have distinct values in the field i'm labelling the objects with. Hence, when I ...
5
votes
2answers
9k views

How to get the data type of each column from a postgis table

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this: column_name | ...
1
vote
0answers
25 views

How can I style a feature that has multiple values for the same field?

I'm opening a PostgreSQL Table on QGIS which has multiple rows related to the same object (think of how you'd register distinct apartments on the same building). I do get to style it based on any of ...
1
vote
1answer
28 views

Find row with a polygon containing a specific lat/long

I have a Postgres table that has two columns – address, and geom (which is a multipolygon without any SRID set, but it should be 2263). I'm trying to find all of the rows that contain a given point, ...
2
votes
0answers
34 views

How to count near intersecting polylines and merge them?

I have a dataset with several polylines created by GPS points of car trips. I am trying to create a simple heatmap where roads with more traffic (where polylines intersect) get darker. I already ...
0
votes
0answers
8 views

how to fetch fields having difference in values from two tables in postgresql

I have two tables with same fields.I want to return fields whose values doesn't match with other table field values. tableA field1 | field2 | field3 | 1 | 2 | 3 | ...
0
votes
1answer
48 views

Using pgRouting through PostgreSQL

I followed the guideline from this tutorial to use pgrouting through PostgreSQL but I can't understand the first instruction: CREATE OR REPLACE VIEW road_ext AS SELECT *, st_startpoint(the_geom), ...
1
vote
1answer
224 views

What is the difference between ST_GEOMETRY and WKT?

What is the difference between ST_GEOMETRY and WKT? Is ST_GEOMETRY just a database type and WKT (well-known text) is a textual representation of the data?
1
vote
0answers
33 views

Cannot store raster in custom database schema

I want to store an elevation raster in a postgis database. However a problem occurs. I use the following command: raster2pgsql -I -C -e -Y -F -s 4326 -t auto dem.tif schemaname.filename | psql -U ...
0
votes
2answers
110 views

How to load all states of TIGER into PostgreSQL?

I downloaded TIGER2014 from the Census FTP and I was wondering how I would be able to load all the geometries at once. I learned about loader_generate_script and loader_generate_nation_script but I ...
1
vote
1answer
69 views

Select points based on value and proximity

I have a set of points in a table, each has a value between 1 and 100. How could I reduce this to the point with the highest value within a radius distance X. So that no point is ever closer than X ...
3
votes
1answer
35 views

QgsDataSourceuRI and left outer join

I'm trying to load data from different tables using left outer joins into a QgsVectorLayer. The approach I'm following so far is the one from the developers cookbook here. The underlying database is ...
5
votes
1answer
107 views

How to load layers from postgres using SELECT statement?

I am trying to load a postgres table (columns extracted using a select statement) from the python console in QGis: The following works fine and the layer loads as expected: uri = QgsDataSourceURI() ...
2
votes
1answer
37 views

postGIS - Error: couldn't load library C:/Program Files/PostgreSQL/9.5/lib/postgis-2.2.dll: %1 is not a valid Win32 application

Environment - Windows10(x64), PostgreSQL 9.5(x64) I installed GDAL, PROJ.4 and GEOS (via OSGeo4W ) Then I installed postGIS via Stack App Builder (I have tried both versions - x32 and x64). In both ...
1
vote
1answer
115 views

Finding all polygons inside of a polygon

How to find all the polygons inside of a polygon? I tried ST_Intersects and ST_Contains but they are booleans variables. I have a list of polygons in WKT format, so I am trying to pull all the ...
2
votes
2answers
345 views

How to get Geography data to GeoJSON in PostgreSQL?

I am using PostgreSQL database and there is a field in a table(area), i.e., public.geography(Points,4326). It contains below type of records "0101000020E6100000BA83D89942334540780B24287ED051C0" I ...
4
votes
1answer
55 views

PostGIS Intersection weird behavior

I can observe a strange behavior with my PostGIS query. I am trying to accomplish the following: I have one polygon layer I have 2 different line layers layer with horizontal lines layer with 45° ...
4
votes
3answers
121 views

Translate PostgreSQL column names in QGIS

I have connected to PostgreSQL and imported the layer to QGIS. The column names are written using English letters. Can the names of columns be automatically translated to another language?
1
vote
0answers
27 views

PostGIS Nearest Neighbor query performance - can it be quicker?

I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I ...
0
votes
1answer
186 views

pg_dump Database that was populated with osm2pgsql

Is it possible to dump a postgres database that was created with osm2pgsql and a .osm.pbf file with pg_dump and import this into a remote postgres database? After running osm2pgsql the database is ...
0
votes
0answers
31 views

Postgis Database: How can I get all gps points between specified timestamps and specified region?

I am totally new to DBMS. I have traces of vehicles in different csv files for each user. Format: name,timestamp,latitude,longitude. 1) How to implement RANGE Query which asks for all the gps points ...
3
votes
1answer
196 views

GROUP BY on geography in postgres 9.4.0 returns wrong/different results

table definition: CREATE TABLE search ( id bigserial NOT NULL, location geography(Point,4326) NOT NULL, location_aggregated geography(Point,4326) NOT NULL, created timestamp with time zone ...
1
vote
1answer
44 views

Why missing address IDs in postgresql/postgis query results?

I have two tables "ad" and "st" in my postgres DB. Table ad has 87 rows while st has 16,060 rows. Following code works perfectly to select st.geom (streets' geometry) within 50 meter radius with ...