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

learn more… | top users | synonyms

0
votes
0answers
49 views

`Execute SQL` in Processing QGIS 2.6

I made a small model with Processing in QGIS 2.6. The final layer should be the result of a SQL query. Now, in the Execute SQL dialog window I can add the query: select name, max(ele) as ele, geom ...
1
vote
2answers
59 views

How can I add another WHERE filter to a PostGIS query?

I have this query, which works well, for an OSM database: SELECT row_to_json(fc) FROM ( SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry, ( ...
2
votes
1answer
112 views

SQL query as part of featureClick in CartoDB.js

I am a complete novice at both Javascript and SQL so am learning as I'm going with this! I have made a webpage that shows a map with a layer of points on the left hand side of the page. What I want ...
0
votes
2answers
251 views

oracle search against intersect of a region that covers the whole world doesnt find any records

I am new to Oracle Spatial, I am trying to implement a service that will take coordinates from a map, and search our database to find any geometries that intersect with that map view. I works fine for ...
0
votes
0answers
72 views

Neighbours list extracted out of polygon regions

I've got a SQL database which contains some coded polygon structures. Those can be extracted as follows poly <- data.frame(sqldf("SELECT ST_astext(geometry) FROM table")) The data.frame 'poly' ...
2
votes
1answer
463 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
517 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], ...
2
votes
1answer
54 views

Why can't I add a Spatialite table with clip results in QGIS?

I have 2 layers and I want to make a clip of layer 1 with layer 2 in SpatiaLite (I'm using the DB Manager within QGIS). That's the code I'm using: create table clipped_lines as select * from lines, ...
1
vote
1answer
110 views

How to query the data using SQL in the infowindow of CartoDB

I want to use a SQL join to merge data from 2 tables and display it in the info-window. When I give my query Select table_2.hos_name,table_2.revenue from table_1,table_2 where table_1.area_name = ...
1
vote
1answer
186 views

MapInfo, SQL data from multiple tab files which have the same columns

New to the GIS forum so apologies in advance for any potential etiquette failures... I'm working in MapInfo. I have multiple tables with the same structure. One of my columns is pipe diameter. I ...
1
vote
2answers
619 views

Function for Selecting Points in a Polygon

I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all ...
1
vote
1answer
29 views

CartoDB - “sql applied has errors” message - where can I see the error text?

I'm still getting my bearings with the interface it seems... When running a SQL query in CartoDB against a table... it seems like a get the correct result, but I also get a see a "sql applied has ...
1
vote
1answer
44 views

send to WFS a custom sql query using jquery

If anyone please, could gimme me a hand on this question. I have a web sql client that the user can write a sql statement and I want to sent it via WFS to geoserver, run it, and based on the results, ...
2
votes
2answers
130 views

Query for numeric field against text field that contains a numeric value

In my database, I have one field, Service_Day, formatted as numeric, which contains a single digit number 1 through 7 (for days of week). I want to compare it against another field, called Region, ...
0
votes
2answers
102 views

Can I include data from multiple rows in CartoDB infowindow?

I have a visualization in CartoDB of a set of data points in table pointlayer. I am using cartodb.js and have a nice map for browser consumption. When a user clicks on a point, I'd like to run a sql ...
0
votes
2answers
121 views

How to use cartodb.js without storing sql in javascript

Using cartodb.js, how can I change the layer definition without building the sql dynamically in javascript and passing it as a parameter. Aside from revealing my table structure (not a huge deal at ...
0
votes
1answer
41 views

SQL in CartoDB to select using OR more efficiently

I made this map a CartoDB map to to show the countries that have banned their citizens working as maids in the Gulf states, I also want to show the Gulf states as one colour. I created this SQL ...
1
vote
2answers
143 views

Multi Value User Input as Variable in SQL Statement

I'm attempting to write a script that takes a user's selection as a parameter and uses that as part of the SelectByAttributes function. The parameter represents field attributes and is multi value, ...
2
votes
2answers
1k views

How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

I have shape column of type geometry and I want to convert X and Y coordinates into Degree Minutes Second format using SQL query and i am using sql server 2008 R2. Currently I got Lat and long using ...
3
votes
0answers
99 views

Preserve datatypes when using SQLite and QGIS

Following Nathan's SpatiaLite/DBManager solution in this answer, I find that once the layer is loaded from DB Manager into QGIS (2.6.1), any calculated fields are appearing as text values instead of ...
0
votes
1answer
75 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', ...
1
vote
0answers
21 views

SpatiaLite / QspatiaLite: get subgroup row ids

I'm using QspatiaLite plugin to join polygon ids to points that are within those polygons. Now I' d like to get subgroup rownumbers for each subgroup of points with same polygon id! Like so.. grp_ID ...
1
vote
5answers
426 views

Selectively removing whitespace from a string with SQL

I have two columns of adresses that I need to join. Column 1 has the adresses formatted as "Some Street 12 A" while column 2 has them formatted as "Some Street 12A." Ie. the last whitespace between ...
4
votes
3answers
438 views

Maximum length of WHERE clause with IN statement

I need to get all features from feature class where their field value matches to input array. To achive this one may set IQueryFilter.WhereClause property similar to 'FIELD_NAME IN (array[0], ...
2
votes
3answers
2k views

Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

I need to build a sql server db trigger to update a date field with the current date when a record is inserted or updated, I think. I will be using the date field as a way to recognize ...
1
vote
1answer
927 views

Trigger a python script to run after a row is added to the feature class

I have a Python script which runs on a SDE feature class daily (as a scheduled task). I would actually like this script to run whenever a new row is added to the feature class (data is captured in the ...
3
votes
1answer
281 views

Triggers for delta tables

I have been facing an issue to capture the delta updates on SDE. We have written triggers on A and D tables to capture the delta updates(to capture the version name based on state id's), however I ...
0
votes
1answer
37 views

Modeling SQL Query to add statistical data to a geometry

Hello I have a problem modelling a SQL Query for the following problem: I have two tables with geometries from Shapefile der Verwaltungsgrenzen (WGS84). Of interest for me are Kreise and Gemeinden, ...
0
votes
0answers
63 views

Query for finding nearest distance and driving time from objects in a different tables

I have 4 postGIS tables: employees_wgs Facilites and (created by osm2po): at_2po_4pgr at_2po_4pgr_vertices_pgr What I want to do is: make a query to populate two columns in "employees_wgs" ...
0
votes
0answers
34 views

How to save SQL-like WHERE as .qqf in QGIS 2.6?

I used to use QGIS 1.7.4. And there was "advanced search". I can save SQL where clause as .qqf. But I installed QGIS 2.6 and I cannot find this kind of things. Could you please teach me how to save ...
6
votes
1answer
211 views

SQL “Case” statement when count(*) is 0

I am trying to write the following SQL command using case clause. with x as (select the_geom as geom from table1 where st_dwithin(the_geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326), ...
1
vote
1answer
80 views

Computing intersections for each geometry in a table

I have a table with many Linestrings and for each of these Linestrings I need to figure out which other Linestrings, from the same table, intersect with it. If I recall correctly this gives me a ...
1
vote
1answer
61 views

How to perform a calculation using attributes of groups of features in ArcGIS?

I have a table with information about 2000 aquifers (thickness, area, depth, volume etc.; there are 20 fields approximately). One of these fields is an internal code, i.e. DK_12_456_XXX; the last 3 ...
1
vote
2answers
130 views

OGR2OGR and SQL: selecting 30 biggest places from a country?

I use ogr2org to filter naturalearth places.shp : ogr2ogr -f GeoJSON -where "ADM0NAME = 'India' AND POP_MAX > 500000" places.tmp.geo.json ne_10m_populated_places.shp I would prefer to select the ...
1
vote
1answer
73 views

OGROGR and SQL: unexpected reserved keyword error?

As I do the following query on naturalearth places.shp : ogr2ogr -f GeoJSON -sql "SELECT * FROM ne_10m_populated_places WHERE ADM0NAME = 'India' ORDER BY POP_MAX DESC LIMIT 1,30" places.tmp.geo.json ...
3
votes
1answer
879 views

Cartodb - SQL query of public visualization of a private table

The team at CartoDB have recently(last few days) made some useful updates that now allow public visualisation maps to reference data in private tables. The change has only been made in the last day ...
3
votes
1answer
294 views

Python SQL connection causes crash in ArcMap

I have the following code which I have striped down to just included the part that is causing a crash in ArcMap. import pymssql # Connect to the Research db using Windows Integrated Authentication. ...
0
votes
2answers
71 views

SQL filter to show one layer only if another layer is missing

I have two oracle tables that represent buildings in my town. The first table represent buildings that are surveyed and measured accurately but doesn't cover all the parts of the town while the second ...
2
votes
2answers
318 views

Spatial join versus classical join

What are the main differences between a spatial join and a classical database join such as an equi (inner) join?
0
votes
1answer
65 views

SpatiaLite - WITH, view or sub-query?

I am running on Windows 8.1 SpatiaLite_gui 1.8.0, SpatiaLite version 4.2.0 and SQLite 3.8.5. I need to interrogate one single spatial table whith those attributes: ID | val | val_1 | val_2 | Geom ...
1
vote
0answers
24 views

Needing to find parent zip code of a bunch of missing zip codes in the US

We're using MapPoint to export zip code / territory combinations. Unfortunately, the export doesn't include PO Box zip codes which mean that we're missing a lot of zip codes The PO Box zip codes are ...
5
votes
2answers
115 views

How to draw radius of a circular point buffer using PostGIS

I have generated circular buffers of points in a shapefile. The generated circles and points share a common ID/key, say pt_id. My question is Is it possible to use PostGIS to generate a third layer ...
1
vote
1answer
930 views

How do I insert a geometry as WKT to SQL Server Spatial with PHP?

I have a web mapping application built on Bootleaf (Leaflet + Bootstrap). Using the leaflet Draw plugin the user can create a polygon on the map. I'd like to insert that polygon into a geometry ...
0
votes
0answers
122 views

ModelBuilder Iterate Row Selection & Variable Expected Fields in Summary Statistics

I have a model using a Iterate Row Selection. The inline variable produced by the iterator is used in the SQL expression of a Table Select tool. Note that this Table Select tool does not use the ...
0
votes
0answers
51 views

Apply raster contraints on single table with multiple files

I have a raster table (pre-created using CREATE TABLE) containing multiple individual raster files that I append piece by piece to a final table. The table itself is in its own tablespace 'gisdir' and ...
1
vote
1answer
855 views

Labeling classes for small roads on large extent maps in ArcMap with SQL statement?

I have many small sub divisions that won't label nice, (lot of overlapping) unreadable. While using the bullet leader symbol. What would be the best statement that would place the labels in a ...
1
vote
2answers
109 views

How to 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
108 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
368 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
76 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 ...