GIS Stack Exchange
GIS Stack Exchange

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

PostgreSQL is an open source object-relational database system that has earned it a strong reputation for reliability, feature robustness, and performance.

PostgreSQL logo

Website: https://www.postgresql.org
PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Main_Page
Wikipedia: http://en.wikipedia.org/wiki/PostgreSQL#cite_ref-4

4192 questions
6
votes
4 answers

What is gin_trgm_ops of PostgreSQL?

I am using a query where the following line written CREATE INDEX trgm_idx ON public.roads_r USING gin (name gin_trgm_ops); Its returning the following error operator class "gin_trgm_ops" does not exist for access method "gin" How can I overcome…
Devils Dream
  • 3,179
  • 9
  • 44
  • 78
3
votes
3 answers

PostgreSQL Queries, how can I handle logic and complex mathematical functions to update a column

I have a Postgres Table of u and v wind velocity components as follows: ugrid vgrid -2.7 -6 -2 -6.3 -1.7 -6.4 I need to calculate the speed and direction as per the JScript Psuedocode below and store the result in two new new columns in…
Mark Cupitt
  • 3,884
  • 4
  • 30
  • 54
3
votes
1 answer

PostgreSQL client version in QGIS

How can I determine which version of libpq that my distro of QGIS is running? When I open the 'about' section, it says 'unknown'. I started looking through some source and doc, and seems like the PostgreSQL Client comes via GDAL. Ultimately, I am…
Adam
  • 31
  • 2
3
votes
1 answer

Drawing line to closest point within single column

I have sets of points that define a roadway segment, and can connect them with St_MakeLine, but I have no field with which to order them, so they zig-zag. Ordering by lat/long/x/y only partially helps, as they curve back on themselves. I would like…
theSchmett
  • 51
  • 3
3
votes
2 answers

Create triangle fishnet

I am looking to create a fishnet of triangles with a specific x,y start location and angle. I essentially want my fishnet to follow the boundaries of a hexagon grid that I have. I found the Jennes repeat shape, but there is no option to anchor it in…
D_C
  • 1,359
  • 10
  • 23
2
votes
1 answer

Inserting union of two tables into another table using PostgreSQL?

I am working on a research project. I have two tables, one of a polygon that represents a severe weather risk area, one that represents the US. I want to merge them into one output polygon. I am stuck with the syntax of how to do this. spc is the…
Matt
  • 21
  • 1
2
votes
2 answers

self intersecting polygon SQl

I am trying to write a sql to query one land parcel for intersections between itself. I can do this fairly easy in QGIS using topology checker but i don’t know why this does not produce the correct results. select * from land_parcel as a …
kartoza-geek
  • 1,161
  • 1
  • 7
  • 17
2
votes
4 answers

postgresql how not to show the first result returned st_distance

I have a query that calculates the distance away from a given point, however currently my query returns the point I am actually querying a distance for. How would I prevent site 1 from showing in my results? Site 1 = 0.00km site 2 = 0.15km site 3 =…
Tom
  • 398
  • 2
  • 13
2
votes
3 answers

how can i use the postgresql's Geometric Types

How can i implement Geometric Types in postgresql db.. Postgresql db is hosted in windows os.. and how can i get whether point is in circle or not? getting errors when i am using predefined functions... Is there anything do i need to install further…
varma
  • 103
  • 1
  • 4
2
votes
1 answer

PG-Strom and spatial queries in PostgreSQL

I would like to start using the GPU on my server in my PostgreSQL queries. I cannot find much information about the use of PG-Strom for spatial queries. The most I have found is that it may be coming in PostgreSQL 9.6. We are beyond that now. So?…
D_C
  • 1,359
  • 10
  • 23
2
votes
1 answer

Copying data from one table to another automatically using PostgreSQL

I have a database with two schemas, "public" and "versioned". In "versioned" I created a copy of the data contained in "public" that I update daily. Is there an automatism for the data that I add every day on the tables in "versioned" to add them to…
GIOVA.A88
  • 189
  • 5
2
votes
1 answer

Split vector layer in PostgreSQL/PostGIS

QGIS has this nice tool to Split a layer into multiple layers based on one attribute and create a new layer for each unique value within that attribute. Is this also possible using PostgreSQL/PostGIS? What would the SQL code look like? Edit based on…
JoeneGB
  • 158
  • 7
2
votes
0 answers

PostgreSQL ST_LineLocatePoint function

I am using the ST_LineLocatePoint function from PostgreSQL. According to what I have read, the description for this function is as follows: Returns a float between 0 and 1 representing the location of the closest point on LineString to the given…
Gary Whitcher
  • 53
  • 6
2
votes
1 answer

PostgreSQL function string concatenation

Problem: Function takes a spatial clause as a parameter of text (more about this later) and appends to the primary sql string. clause = '((44<=NeLon AND 45>=SeLon AND 37<=NeLat AND 38>=SwLat) OR (SwLon<=45 AND NeLon>=44 AND SwLat<=38 AND…
user9491577
  • 113
  • 1
  • 10
2
votes
1 answer

Can't set a geometry data type in PostgreSQL

I have the datatype geometry defined. But I can't set it to one of the columns geom. as shown in the figure.
Andre Ahmed
  • 149
  • 1
  • 1
  • 6
1
2 3