Tagged Questions

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

learn more… | top users | synonyms

0
votes
1answer
35 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
54 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
329 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
36 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
92 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
114 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
72 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 ...
0
votes
1answer
272 views

SQL Geometry Query similar to Select by Centroid

Does anyone know if it's possible to query SQL geometry by something like 'centroid of polygon'...similar to ArcMap's select by location? I'm using SQL Server 2008 R2 as the DBMS. I have two polygon ...
0
votes
0answers
38 views

Postgresql (soln would be basic SQL I think): ORDER by distance to a point and return those distances in array pairs w/ the full records

so I know there have been some questions asked about how to calculate distance using postgis and how to order results by their distance from a respective point. I'm not very talented at SQL (at all) ...
0
votes
0answers
11 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
3answers
62 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
36 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 ...
1
vote
2answers
114 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
0
votes
1answer
176 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
0answers
41 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
83 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
251 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
115 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
161 views

In cartodb, how can I insert a new row in a table via API?

We've discovered cartodb and we want to use it in our project, but in the API documentation we've only seen the sql.execute() method (docs) in order to insert a now row in a table. Is this the way to ...
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 ...
2
votes
1answer
80 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 ...
2
votes
2answers
174 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 | ...
4
votes
1answer
201 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
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
1answer
43 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 ...
0
votes
0answers
45 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 ...
1
vote
2answers
678 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
39 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
96 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
72 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
33 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
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
54 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
156 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
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
79 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
64 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
79 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
76 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
99 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
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 ...
0
votes
2answers
48 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
48 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
37 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
310 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
45 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 ...
2
votes
1answer
558 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
193 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
0answers
89 views

Group Nearest neighbor query in SQL Server 2012 spatial database

I am looking for way to perform GNN query over road networks in SQL Server. Any pointers