Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

learn more… | top users | synonyms

0
votes
0answers
10 views

How do use an IF THEN statement in an sql Select expression?

I need to select the top 3 records from a table, but depending on an inline variable from an iteration, occasionally one of those top 3 records should not be selected and should be replaced by the top ...
0
votes
0answers
14 views

ERROR: operator is not unique: unknown || geometry

EXECUTE 'INSERT INTO Panden_intersect(St_ints,pnd1,pnd1_area,status1,dtm_strt1,pnd2,pnd2_area,status2,dtm_strt2,CtrDist) Values ( ...
1
vote
1answer
37 views

What options are there for geolocation-aware SQL databses?

I need to implement an SQL database that can make queries using geo-location based logic. e.g. select rows within 500m of coordinate x, y What options are available to do this with sqlite, mySQL, ...
0
votes
0answers
16 views

ogr2ogr export to KML from SQLite, when using SELECT for fields Linestrings are lost

I'm using: ogr2ogr -sql "SELECT * FROM 'trails_utm_utf8' ORDER BY name ASC" -f KML trails.kml trails_utm_utf8.sqlite -s_srs EPSG:900913 -t_srs EPSG:4326 to reorder, re-project and export to KML ...
2
votes
0answers
35 views

SQL Query does not work in ModelBuilder (but works elsewhere)

I am having issues getting a simple SQL query to work in model builder. It works fine when I use it with the standard "Select features by attributes tool", but ModelBuilder doesn't find the required ...
0
votes
2answers
33 views

Postgis: Point-in-polygon check + feature attributes inheritance

I have two tables in Postgis 2.1 database: Polygon features with a reach attributive information; Regular point grid; I would like to get a SQL query returning the regular point grid with ...
3
votes
1answer
488 views

What does “foo” stand for in this PostGIS command?

The help page for the PostGIS function ST_PixelAsCentroids has this line of code as a usage example: SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM dummy_rast ...
0
votes
1answer
35 views

selecting elements of a specific geometry type in PostgresSQL/PostGIS

I was reading a postgis tutorial and noticed that unlike working with shapefiles, one can actually store different types of geometry in postgres. In a table created with multiple geometry types, the ...
1
vote
3answers
37 views

ST spatial queries to select multi-level location

I'm trying to write a query in SQL Server using 1 set of points and 2 sets of polygons. The polygons overlap, and I only want to select the points that fall inside of 1 polygon but not the other. I ...
1
vote
1answer
36 views

Loading raster in postgis and link it with an existing table

Relatively new to PostGIS and I am currently exploring the options to make one of my PostgreSQL Tables spatial. Essentially I want to store a raster object in a table that also contains essential ...
1
vote
1answer
24 views

How to create non-duplicate lines between all selected points in Spatialite?

There is a nice answer for the similar question for PostGIS. I tried to adapt it for my case where I need to connect all points from a point layer, but unfortunately I get duplicate geometries: lines ...
2
votes
1answer
19 views

SQL Server equivelent to Arcmap Intersect for polygons?

I have three different sets of GEOMETRY Polygon datasets in SQL Server that completely overlap. I need to create a new dataset that has a polygon for each unique area of the three layers. So ...
2
votes
2answers
54 views

Get Coded Value Domains from a Oracle geodatabase using PLSQL

i'm trying to get all coded value domains from a Oracle geodatabase. I dont have access to arcmap. I only have access to oracle 10g database. SELECT I.NAME Domain, ...
0
votes
1answer
26 views

Get a sql view to persist in a CartoDB visualization

I'm new to CartoDB and so far impressed. But I'm struggling with how to get a sql query to persist in a visualization. I type it in, like 'SELECT * FROM env_feedlots where active = 'Y' and BIRDS > 0', ...
0
votes
1answer
33 views

How to preserve GPX track point (trkpt) info when performing ogr2ogr with sql ST_union operation

I am performing GDAL ogr2org operation with ST_union(Geometry) on tracks from GPX file to split those at intersection points. Track points have elevation and depth info. In GPX track point entry looks ...
2
votes
1answer
49 views

If do not want to use pgRouting then what?

Good evening fellow GISers, I am interested in creating an online routing application. So supposing I have all the data I want into a postGIS database and loaded onto GeoServer and I am willing to ...
0
votes
0answers
46 views

Geometry datatype to latitude /Longitude MS SQL Server

I need to convert Sql Server geometry datatype to latitude /Longitude I have just imported a shapefile to my table (geometry) This query does not work:- select geom.Lat as [Latitude], ...
0
votes
1answer
13 views

create Geoserver SQL Views

I want to create an SQL View in my local Geoserver (2.4.4), acccording to the handbook. http://docs.geoserver.org/stable/en/user/data/database/sqlview.html They say: In order to create a SQL View ...
2
votes
0answers
25 views

SQL Error Message: Find and Replace for Date Field

Hi, I am attempting to use the find and replace function within the Attribute Table Options to replace fields that contain dates in the following format: month/day/year (e.g. 03/14/2014). The ...
2
votes
1answer
34 views

How to populate geography point using ST_MakePoint(long, lat)

I have a table called customer_campaign, and there is an empty column geom(geography) in it. So I want to fill it up by using ST_MakePoint(long, lat) in PostGIS, lat and long are two columns in the ...
0
votes
0answers
38 views

Error selecting geometry from Oracle DB

I'm trying to select a geometry column as well-known text from a feature class in an Oracle 11g/ArcSDE 10.1 geodatabase. (This is in a Python script using pypyodbc as a driver). This is my SQL: ...
9
votes
5answers
280 views

GIS analysis to find duplicate geometries

I have a big shapefile that contains all the buildings and houses of the town that I work in (approx. 90,000 features). The data of the buildings/houses are saved by the town's surveying engineers and ...
0
votes
1answer
71 views

dealing with geometry that should be geography (bad coordinates)

I'm working on a project with a few publicly available datasets. One of them is from the USGS, it's their prediction of peak ground acceleration in 50 years. Here's a link, and here's what it looks ...
3
votes
2answers
72 views

How can I extract all distinct values from a PostGIS Raster?

I am working with an integer raster in PostGIS 2.1 and I would like to extract all distinct values in that raster. I was hoping to be able to do something along the lines of the following query, but I ...
1
vote
0answers
27 views

Tables from SQL Server to QGIS Layers

I'm a beginner SQL and GIS user and this my first time using stackexchange, so apologies if I'm asking the question properly. I'm trying to open some MSSQL tables in QGIS. I established a connection ...
6
votes
1answer
155 views

How to use ST_DelaunayTriangles to construct a Voronoi diagram?

With PostGIS 2.1+ we can use ST_DelaunayTriangles() to generate a Delaunay triangulation, that is a dual graph of its Voronoi diagram, and, in theory, they have an exact and reversible conversion. ...
0
votes
0answers
5 views

PostGIS-Layer: sql query from style rules

I’m using PostGIS layers which are build from OSM-Data (osm2pgsql). Now I like to build a sql-query to filter the needed objects like "waterway" = 'river'. But in the layer-style I already have that ...
0
votes
0answers
15 views

Adding a column to several feature classes in a file gdb using ogr2ogr

I'm trying to add the same column to 25 feature classes in an ESRI FileGDB. I managed to do this for one feature class by using ogr2ogr: ogrinfo C:\temp\test.gdb -sql "ALTER TABLE table01 ADD COLUMN ...
1
vote
2answers
36 views

How to add the values in two columns to a new column on a sync table?

We have field data coming in from an app platform and we want to drive a CartoDB visualisation to help with planning. A bunch of rangers go out surveying sites to count crocodiles. For each site, the ...
2
votes
2answers
39 views

SQL 2012 code that spatially polyline fine (join file) to a point file (target) and provides Distance Field

I am currently working in ArcMap 10.2.2 but would like the SQL code to create the same output as that provided by the Spatial Join tool. Target feature = point, Join Feature = polyline, Option ...
3
votes
1answer
55 views

PostGIS: Get closest point between geometry and Multipoint

I think my question is not that difficult for GIS professionals, but i didn't found an answer to my problem (even not in other questions). Problem: I have given two tables (table1,table2) with Points ...
1
vote
1answer
64 views

Definition Query to exclude certain values and return remaining values

I have been trying to create a Definition query to symbolize a set of water features. I cannot edit the .shp file and I want to exclude a multiple values. I have tried using NOT but have had no luck. ...
2
votes
1answer
43 views

Perform a group by query on geodatabase in ArcMap

I have a geodatabase called predictions.gdb and a feature class called output_gis_120_Project in that database. I imported this feature class as a layer in ArcMap. The attribute table with columns ...
2
votes
1answer
56 views

Model Builder Query by date

I've created a model that does several spatial joins based off of a lat/long in a table. In the table, I have a "Modified_date" which gives year, month, date, time. This model is set to run ...
2
votes
3answers
70 views

Find point close to points from postgis table

I have a postgis point table with a large list of cities. I need to find duplicates in it, but only if they are 10km close each other, which means that is the same city. For example, I have 5 points ...
1
vote
1answer
33 views

How to perform SQL query using .STContains in MS SQL Server

I have a table that stores polygons and a table that stores points with a value field. Both sets of data are stored using the Geometry type in MS SQL Server. I have a simple requirement - I need to ...
0
votes
1answer
44 views

About the storage of GIS attributes

I'm quite new to the GIS world and I currently have some technical questions for which I have no answers yet. I'd like to know the good way to store the attributes for a feature using the PostgreSQL ...
1
vote
1answer
55 views

Can't select geometry from Oracle DB using SQL

I'm trying to select the geometry field from an ArcSDE feature class in an Oracle 11g database (inside of a Python script via PyODBC). This is my query: SELECT SHAPE FROM USR.FEAT_CLASS This is ...
2
votes
2answers
90 views

Definition Query / SQL problem in ArcGIS

I have been working on some Definition Queries for layers in ArcGIS (10.2.0.3348). The layers connect to data in a File Geodatabase. Now I have attempted to implement the following query (1), that ...
2
votes
1answer
46 views

Find nearest distinct features to a point

I am trying to select a single distinct features closest to an XY point in PostGIS. So I can find a single record for each distinct code by using: Select Distinct ON (code) * FROM table; Which ...
1
vote
1answer
58 views

Invalid SQL-Statement error when querying file-geodatabase table date field with arcobjects

I want to query a DateTime Field in a table in a file-geodatabase and I keep on getting the error "invalid sql statement" (Error Code: -2147220985) while that sql statement does not give me an error ...
0
votes
1answer
63 views

Cannot load postgis layer from sql string in pyqgis

I'm trying to load a layer using the following code in qgis python console: sql = "(select the_geom, ID from mytable)" uri = QgsDataSourceURI() ...
0
votes
1answer
38 views

geoserver fails to start with jdbcconfig community module

I have installed the geoserver JDBC config community module, but now geoserver fails to start. I have set the properties to connect to an oracle database like this: #Default GeoServer JDBC config ...
2
votes
1answer
111 views

SQL query to have a complete geojson feature from PostGIS

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature. so far I tryed to modify the ...
1
vote
2answers
65 views

Query to find all lines connected to other

I have problem with one spatial (Oracle) query. Simplified overview of the problem is: I have two tables: CREATE TABLE thin_lines ( id NUMBER PRIMARY KEY, geom SDO_GEOMETRY), color ...
3
votes
2answers
65 views

Buffer multiple roads to find points within and compare?

What I am trying to do is buffer a main st and a side street and get all of the points that intersect that buffer, however they need to be mutually exclusive. In the example below, my results would ...
1
vote
0answers
59 views

Making Map with Color Ramp Using SQL Database Intergration?

This could be a trivial question, but I've been kind of stuck on it for awhile. I'm trying to construct multiple maps that reflect aggregated data showing overall risk by county in a SQL Database ...
2
votes
1answer
52 views

Spatial joining syntax

A standard PostGIS point-in-polygon query might look like this. (example tutorial) SELECT p.*,b.polyname FROM points p, shapes s WHERE ST_Within(p.the_geom,s.the_geom) Imagine this was not a ...
1
vote
1answer
43 views

Geoserver SQL Parameter View

I'm trying to use Geoserver Parameter SQL views, but I'm having trouble getting geoserver to recognize the parameters (and I'm not able to follow the documentation - it's not very clear to me). Here's ...
2
votes
2answers
37 views

PostGIS SQL NOT EXISTS Statements? [closed]

I'm trying to query my PostGIS database for ID of people who did X but did not do Y. I've attempted the query below, but it does not return any results. I'm very new to SQL so I may be overlooking ...