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

learn more… | top users | synonyms

5
votes
1answer
429 views

How to use %value% (from For iterator) in SQL expression of Calculate Field in ModelBuilder?

I have a shapefile with data of fish specimens and the coordinates where they were tagged for research purposes. One of the fields in the attribute table of these shapefiles is the fish length in ...
-1
votes
1answer
78 views

How to Label Field With Multiple Selected Values?

I have a shape named "Subdistrict" and it shown on Attribut Table like this : No | subd | village 1 | town1 | v1 2 | town1 | v2 3 | town1 | v3 4 | town2 | v4 5 | town2 | v5 6 | town2 | ...
0
votes
1answer
54 views

Choropleth map turns one color when filtered, sql error

My data is generating a choropleth map perfectly but when I try to filter (both in the filter tab and by writing my own SQL) it turns all one color and tells me I have an SQL error. This happens even ...
1
vote
2answers
1k views

QGIS Select by Expression

A basic QGIS 2.2.0-Valmiera, Select By Expression - Field Calculator question. Introduction I am a geologist searching through a geological database (see attached figure). I have a shape file and ...
1
vote
1answer
49 views

Keep column titles when exporting SQL query in MapInfo

Is there a way to keep column titles when exporting a query in MapInfo as a csv or a new tab. This is my result: And my export looks like this: It's not a major issue, I jus like saving myself ...
0
votes
1answer
41 views

How do I show the created_at column at my infowindow?

Fields like created_at, updated_at or cartodb_id are a greyed out thus not allowing us to add it to the info window. Is there a way to extract the data from default fields in cartodb?
0
votes
2answers
133 views

New line in MapInfo SQL Select

Is there a character I need to use to state a new line in the MapInfo SQL Select box (e.g like ; in SQL Management Studio) or is this not possible. The highlighted (yellow) area is where the new line ...
1
vote
0answers
92 views

How to Build Query Table from two OLE DB tables using ArcPy?

When I run the following code to create a subset of a table, the error says "An invalid SQL statement was used" [Query]. The error shows in line 39. Is there any idea you can figure this out? import ...
1
vote
1answer
43 views

why is the CDB_QuantileBins function not found in cartoDB?

What is wrong with the following SQL query on my data? I found pretty clear documentation (1,2) that the function is supposed to work. SELECT CDB_QuantileBins(array_agg(numeric_column_of_mine), 10) ...
0
votes
1answer
73 views

how to run a PostgreSQL query on CartoDB tables?

I want to run a VACUUM FULL on my table, but I don't think it's working. Simply sandorl.cartodb.com/api/v2/sql?q=VACUUM&api_key={MYKEY} results in long output, apparently only repeating for ...
0
votes
1answer
64 views

CartoDB simple process

I'm attempting to create a simple process with CartoDB, I have a layer with number of polygons, I want to to run a SQL with a multipoint line to report which polygons were intersected.
1
vote
2answers
264 views

How to select objects by N:M join?

I have a layer with some geometries and a data layer that defines an M:N relation. Is there any way I could select (on the map) objects that are in relation with specific id? Basically I would like to ...
0
votes
1answer
24 views

whereClause syntax for Cost Distance parameter

I am looking to create a sql expression regarding point feature class and selection based on 'Name' field to pass to arcpy.sa.CostDistance() param[0]. similat to: savepath = env.workspace = ...
0
votes
1answer
91 views

How to create SQL statement to only return chainage values every 500m?

I have a list of chainage values every 100m (as a whole number) and I am trying to create an SQL statement to only return values every 500m. Any ideas? Using ArcGIS 10.1
2
votes
1answer
109 views

Conditional SQL Query

I've been running a code that searches a desired field in a feature class and performs an specified SQL query based on whether the field is string or double. The variables are just temporary fillers ...
1
vote
1answer
93 views

SQL within Python Script to get only part of the expression by GetParameterAsText

I was wondering if anybody could help me. Im trying to create a tool within ArcMap using a python script. Within the script i've been trying figure out how to change it so that I only need to get ...
1
vote
0answers
150 views

PyQGIS Dialog Plugin not resetting

I have written a PostGIS search plugin that works pretty well, except two things that I just cannot figure out a solution 1) When the dialog closes and then is reopened the previous query is still in ...
1
vote
0answers
42 views

Is it possible to retrieve lost versioned data in SQL Server Management Studio?

One of my staff accidentally deleted a feature class she was creating in her version before I reconciled it. I have grabbed a back up of the database from yesterday in SQL Management Studio. Is ...
0
votes
2answers
60 views

How can I optimize this point-linking query?

I have the following query, and am wondering how it could be optimised to run faster: WITH gps_nodes_1 AS ( SELECT "NODE_ID", "geog" FROM gps_nodes WHERE ST_Distance("geog",ST_GeogFromText('POINT( ...
0
votes
1answer
63 views

Set ArcGIS Tool Parameter to Obtain From it's own Source?

I am building a python tool in ArcGIS. When I build the parameters I will have 3: Choose a shapefile (parameter data type = shapefile) Choose an SQL query (parameter data type = SQL Expression) ...
1
vote
1answer
45 views

How to refer to a variable from the conditional statement in the ExtractByAttributes function in ArcGIS 10?

Trying to use the ExtractByAttributes for rasters so that I have multiple attribute values that I want to extract. This works fine: myraster = arcpy.sa.ExtractByAttributes("source_raster", "VALUE IN ...
2
votes
1answer
546 views

is there any direct access to Mapbox data? (similar to CartoDB's sql api)

I've been asked to add a form to a map that will write data in the background (e.g., ajax to a magic file that does datawrites and some emailing, notifications etc) and a new marker will be added to ...
0
votes
1answer
65 views

How can I combine two datasets with the same schema in to one table?

So I have two tables (in CartoDB/PostGIS) that are from two tiger shapefiles that have the exact same attributes, just in different counties. I want to combine them in to a single table, just ...
3
votes
1answer
812 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 ...
0
votes
1answer
457 views

How can I show numbers as percentages in CartoDB?

I would like to format numbers as percentages in cartodb. What are the best functions to display numbers with decimals, comma separators or percenteges? Thanks
0
votes
1answer
39 views

Flag More Than One Space in SQL

I am working on an XP machine with FME'13 and I have a Address field and I have to find the spaces(middle spaces and not the leading and trailing ) in each and every row. St._Petters_Road is the ...
-1
votes
4answers
1k views

What is a SQL Query to all values in a column with Select by Attribute in ArcGIS? [closed]

I would like to select all values for an attribute using select layer by attribute to use in model builder and hopefully save the expression for automation purposes. If I have an attribute named ...
0
votes
1answer
354 views

How do I query XY Event Layer in Model Builder?

I am trying to build a model to input table data, make xy event layer and then select features by an SQL expression and make that a shapefile. What tool should I use for the SQL expression to select ...
1
vote
3answers
258 views

How do I use SQL in the field calculator to populate a new column?

Courtesy of Geofabrik I have a "places" point shapefile which includes in the attribute table a column named "type". As can be seen in the attached image the column contains fields such as "city", ...
1
vote
1answer
138 views

MSSQL server 2008 dissolve sql query

I have a set of spatial objects in a MSSQL server 2008 R2 trying to dissolve adjoining objects where one of the attributes are equal. The table below is somewhat representative to my original table: ...
0
votes
0answers
21 views

GeoDatabase user access based on administration boundry

I have silverlight application consuming Map service. Now I want to create user login. The scenarion is somethinkg like when a user logs he should only be able to query on data belongs to his area. ...
0
votes
2answers
77 views

sql query to get origins and destinations

I have a table of cities with an ID and geometry (200K rows) and a table of travels, with only two ID: idfrom, idto (7K rows) I'm wondering how could I select the geometries for each one in order to ...
2
votes
2answers
226 views

How can I create a hexbin layer using the SQL and CartoCSS parameters in cartodb.js?

CartoDB has a great hexbin visualization wizard baked into the client dashboard: However, I like using cartodb.js to customize events and interactions; in particular, I'd like to make styles ...
-1
votes
1answer
397 views

Shortest Path Dijkstra

I would like to calculate the shortest path with dijkstra algorithm. sample query SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra(' SELECT gid AS id, ...
0
votes
1answer
121 views

How to cut/split multipolygon with blade (linestring) into two pieces using PostGIS? [closed]

I struggle to do split operation in postgis I tried to build a query but Im always fail. Id like to cut a multipoligon with a blade (a linestring) into two pieces. These two objects have specific ...
1
vote
1answer
77 views

Rewrite query to match two points with a linestring in a different database table

OK, not sure if I've worded the title brilliantly, but here goes: Suppose I have: a table "gps_nodes" containing columns: "EASTING" (containing longitude data) "NORTHING" (containing latitude ...
3
votes
1answer
746 views

PostGIS intersection function

I have a problem with intersection function. I built my query following http://manual.linfiniti.com/en/postgis/geometry.html, 5.7 section. I made my clip object, a red linestring frame: But after ...
5
votes
3answers
869 views

Sqlite, Python 2.7 and Spatialite

I am trying to spatially enable my sqlite database: import sqlite3 db = r'c:\temp\mydata.sqlite' conn = sqlite3.connect(db) conn.enable_load_extension(True) cur = conn.cursor() cur.execute(r"SELECT ...
1
vote
2answers
273 views

How to form SQL Expressions with mix of integer/numeric and text/string fields/values in ArcPy?

I am trying to create a query for the select by attribute function within a Python script.This is my current code. lot = '96' dp = 756421 lotFile = ...
1
vote
1answer
262 views

Calculating difference between bearing values in degrees

Suppose I have 3 geographic points, A, B and C. I can calculate the bearing of B from A (in degrees) using this SQL snippet: degrees(ST_Azimuth(geogA,geogB)) as brg_a_b ...and the same of C from ...
4
votes
2answers
786 views

SQL Syntax to extract (convert) Strings with Degrees Minutes to Decimal Degrees

I have a PostgreSQL/PostGIS database with a two string fields containing Lat and Long coordinates in degrees and decimal minutes, like this 45° 24.5' N 3° 43.01' E. How can I convert them into ...
3
votes
1answer
81 views

Use selected value in postgis function

How can I use the selected value from a SELECT statement in a PostGIS function. e.g. using the geom_way from SELECT geom_way FROM osm_2po_4pgr ORDER BY ST_Distance(ST_GeomFromText('POINT(21.323329 ...
1
vote
1answer
44 views

How to do “random” distribution of certain classes to certain amount of polygons with respect to spatial constraints

I have a polygon layer. Now, I would like to assign/allocate/classify these Polygon into 13 different classes respecting spatial constraint, in my case: neighboring polygons (sharing line or nodes) ...
1
vote
1answer
827 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 ...
2
votes
2answers
201 views

Can I use the LEFT (string) operator in ExecuteSQL()?

In QGIS I can use the LEFT operator on a dataset like this: LEFT( "Name2",3) ='031' What is the syntax for using this expression with ogr in python? Isn't this the right syntax? ...
1
vote
1answer
306 views

How do you create a CartoDB viz with a dynamic SQL statement?

I want to create a CartoDB viz where the where parameter in my SQL statement changes dynamically at runtime. When I share my CartoDB viz from the dashboard, I can't help but notice the the SQL ...
2
votes
0answers
38 views

Multiple Cursors

Below is the sql statement which I have explained below. Declare Cursor c1 is select store_number ,0 as total_area,0 as BG_ID ,0 as store_geom from table1 where CLIENT_ID= 1 and ORG_ID ...
6
votes
2answers
2k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
2
votes
1answer
2k views

Trouble with INSERT in PostgreSQL 9.1

I am trying to execute the following: INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) SELECT (cp_east, cp_north, ...
2
votes
2answers
113 views

shp2pgsql mangles date field — quotes data as if it were char or varchar

I'm attempting to import a shapefile into postgis via shp2pgsql. Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql. ...