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

learn more… | top users | synonyms

2
votes
2answers
152 views

Problem with SPIT (error while importing layers to POSTGIS)

I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here: SPIT ...
7
votes
3answers
3k views

Label points at regular intervals using ArcGIS for Desktop?

I'm working with lines on roads represented by a large number of sequencial points. Since labeling all of them would make the points impossible to identify, I'd like to know if there is a way to label ...
2
votes
2answers
140 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 | ...
0
votes
1answer
34 views

I need to test a condition within the sql in postgis.

I need to test a condition within the sql in postgis. My sql: SELECT (zoneamento3_32722.zt3), logradouros32722."NMIDEABR", logradouros32722."OBJECTID", ...
0
votes
2answers
49 views

How to join data in postgis tables to form layer with subset of attributes

In QGIS I am trying to join data from two postgis tables. I can not figure out how to do this at sql because add layer tools seem to allow one to select just one table to filter on. Instead I ...
2
votes
3answers
258 views

SQL expression error for Select Layer By Attribute

arcpy.MakeFeatureLayer_management("N05-12_Station2_JGD.shp","station_12") year=2012 arcpy.SelectLayerByAttribute_management("station_12","#",'"closing">=%d AND "opening"<=%d' % year) ...
0
votes
1answer
34 views

managing postgresql database table using the pgadminIII sql tool

i have created a database using postgresql 9.1 and a table containing parcel information for a town council database on a windows 7 OS. i created the geometry column using the SELECT ...
0
votes
1answer
85 views

SQL Make Table (from .mdb) to Point Shapefile

For my school assignment, I must compose an SQL statement which retrieves data from a database in .mdb format. I have no problem developing the SQL statement. It is a requirement that we have an ...
1
vote
2answers
100 views

Help needed with incorporating SQL query into ArcGIS python script

I'm new to python and have been trying to learn how to use it within ArcGIS. I have primarily been using the free Penn State courseware available here - http://open.ems.psu.edu/courseware I have ...
0
votes
1answer
67 views

MYSQL 5.6 Cluster / chain points by distance to any other point without millions of calculations?

I have a table of approx 10,000 - 20,000 dynamic points across the globe stored as lon/lat points. I want to cluster any point within (roughly) 3km of any other point into one point (and return the ...
5
votes
3answers
538 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 ...
5
votes
1answer
2k views

Registering SQL Spatial View in ArcSDE and updating Row_ID?

I am having issues registering an inner join SQL view with a spatial column in ArcSDE. Any help would be much appreciated. Here is the background info: ArcSDE 10, SQL Server Enterprise 2008 R2, SQL ...
0
votes
3answers
56 views

AVG and GROUP BY in ArcMap

I have a Road Network shp file.(attributes include Names and Volumes ) Each road in my network consists of many links with the same name and different volumes. I want to display Road Names and ...
0
votes
1answer
152 views

How to write to Sql server from MapBasic and insert rows

I have created a table in Sql server 2014 with the columns: Id (int), Geom (geometry), RouteId (int), Desc (varchar(MAX)) I have succeeded in making the table mappable through MapInfo I have checked ...
0
votes
1answer
31 views

PGrouting encapsulated queries error [quotes problem]

I'm facing a quote problem on a pgrouting query. What I'm thinking is wrong is that my query only works if I use one, then four, then eight quotes on encapsulated queries. Is there a way to simplify ...
0
votes
0answers
9 views

Epoch Time Incorrectly Auto-formatted As Int

I'm accessing seismic data from a USGS geojson (set to sync in CartoDB). When the data is brought into CartoDB the column is set as an int when it should be a long int. What is the best way to ...
0
votes
0answers
35 views

CartoDB - renamed table and changed API calls but still failing

I used the SQL API to make a new table with some data from an existing table. I also renamed all the API calls in my code to point to the new table. However, no point markers show up on my map now. ...
1
vote
1answer
82 views

Counting event points by categories and matching to polygons, normalized by population

I'm new to CartoDB, but I'm a seasoned user of ArcGIS. My university was sorely lacking in GIS training; I only learned how to create static maps, not web content. After graduating 3 years ago, I'm ...
1
vote
2answers
204 views

Sql Query for percent of buffer filled with objects

Hi I am trying to write a query to find the percentage of a buffer polygon (Called 250_Buffer) that is filled with or contains other polygons in another layer (Called Development) any help that you ...
0
votes
0answers
102 views

Geoserver SQL View layer not displaying on map

I am trying to create a SQL View in GeoServer from my SQL Server 2008 R2 data store. It is a very basic query: SELECT [Id] ,[Location] FROM [schema_name].[table_name] I set the Id column, ...
1
vote
1answer
28 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) ...
2
votes
1answer
69 views

Get list of all tables in CartoDB account with SQL statement?

How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement? This returns null: SELECT table_name as t FROM information_schema.tables WHERE table_schema ...
1
vote
0answers
38 views

How to determine distances of points from closest line? (Mapinfo or QGIS) [duplicate]

I have a set of points and a set of lines. I'd like to know distances of the points from the closest line... answering the question "how many points are within 500m to 1000m of one of the lines, how ...
0
votes
1answer
39 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
votes
1answer
65 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
0answers
33 views

Why there is no output (path) for Dijkstra in pgrouting

I have a road network table and I am implementing pgRouting. everything was working fine previously but now there is something odd here. I am getting no route now. I am checking through sql query in ...
2
votes
1answer
201 views

How to Buffer based on SQL expression?

Is there any way to optimize this code? It creates two buffer feature classes based on two different fields in the shapefile attribute table import arcpy from arcpy import env ## Set Workspace ...
1
vote
2answers
510 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 ...
5
votes
3answers
8k views

How to convert MultiLinestring to Linestring?

I have a multiline string and want to convert it to a linestring in order to use some functions that only work with linestrings. I used ST_Dump() and got a collection of my geometries. If I merge them ...
1
vote
1answer
38 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
38 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
87 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
66 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
2answers
141 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
62 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
52 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.
0
votes
1answer
21 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
0answers
67 views

How do I use Arcpy to label based on two different expressions, then convert to annotation?

I am trying to open an mxd and turn on labels before converting them to annotation. I would like to turn on labels with 2 different classes, based on 2 different expressions, then convert them to 1 ...
0
votes
1answer
60 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
74 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
73 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 ...
3
votes
1answer
209 views

ArcMAP SQL Statement Iterate Feature Selection 'IN' 'BY' 'IS' - escape character

We have a problem when selecting Features from a Feature Class in ArcMAP 10.2 using the Iterate Feature Selection tool in Model builder Our dataset has a Attribute Field with two character codes like ...
1
vote
0answers
92 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 ...
5
votes
1answer
3k views

Arcpy cursors, WHERE clauses, and date/time fields

I'm having trouble making a selection of rows that meet certain date criteria using my arcpy.UpdateCursor(). I need to update records in a field in a geodatabase table with an ascending value based on ...
1
vote
0answers
37 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 ...
1
vote
1answer
78 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: ...
5
votes
2answers
1k views

How to export a PostGIS raster to PNG?

I have a raster which is split into 30 tiles and I want to dump them all into PNGs. I've read the documentation http://postgis.net/docs/RT_ST_AsPNG.html but still don't know how to dump it well. If ...
0
votes
1answer
45 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) ...
0
votes
2answers
43 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( ...
2
votes
1answer
270 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 ...