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

learn more… | top users | synonyms

4
votes
0answers
84 views

Create polygon from lines in PostGIS

I'm trying to divide a polygon in smaller polygons from a line, but I think I can't use the st_split function. What I need is to create small polygons inside a big one, using linestring grid. I've ...
0
votes
0answers
31 views

Set alias for subfields when performing query

As mentioned within the ESRI-docs we can set an Alias for the subfields of an SQL-query performed onto a file-geodatabase as follows: IQueryDef2 filter = (IQueryDef2) ...
2
votes
1answer
46 views

How to create lines from coordinates?

I have a data table about national bus lines, with timestamps, vehicle IDs, line IDs (which line the bus is moving on), and coordinates(geom). I can add my points to QGIS on show them on OSM, but I'd ...
1
vote
1answer
39 views

Get lat and long in two columns as floats for all points in MULTILINESTRING in PostGIS?

ST_DumpPoints(geom) gives me something like this: ("{1,1}",0101000020E61000002DEDD45CEEB362C0CE1951DA1BC24E40) ("{1,2}",0101000020E6100000C94AD826F1B362C09D4B7BC72CC24E40)... Where I want it to ...
1
vote
1answer
21 views

postgis: union of faces of st_dump

I am trying to make a web application that renders 3D buildings (LoD1). I have a PostGIS (2.2.1dev) database which contains the 2D geometry of the buildings and the height. I used st_extrude to make a ...
1
vote
2answers
114 views

Checking if SQL where clause is valid with Make Feature Layer?

Unfortunately it's possible to apply an invalid SQL where clause when using arcpy.MakeFeatureLayer_management. This can cause issues later in a script. For example, I created a feature layer and ...
1
vote
3answers
64 views

Intersection of 2 Linestrings (not interpolated)

I am struggling with a query in POSTGIS I want to find linestring geometries where the geometries are containing the same point. I don't want to calculate the intersection points. I only want to ...
1
vote
3answers
47 views

Is a SQL String Aggregate possible in arcgis?

I have created a feature class from the results of a 1:many join between a table and a polygon layer in ArcGIS 10.3. The polygon layer contains land parcels and the table contains names. Where a ...
0
votes
1answer
31 views

Parcel Owner to see only his parcel in map application

How can i create a map where user can only see his parcel. parcel are defined has one unique ID. I have Arcgis online Server and Desktop Advanced license. the map to be published in a web application ...
0
votes
1answer
32 views

Negative numeric values in Geoserver sql view params

I want make some query based in lon/lat values of a specific place. A simple example is this: SELECT C.tipo, C.geom FROM climas AS C WHERE ST_Contains(ST_SetSRID(C.geom, 4326), ...
5
votes
5answers
4k views

ExecutING SQL script in ArcGIS field calculator?

I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use ...
1
vote
1answer
61 views

Postgis/SpatiaLite SQL query to connect MySQL, Access or Oracle database

In SpatiaLite or Postgis, how can one create a SQL query to read a table from a MySql or Oracle database? I don't know **the correct SQL syntax to connect to a table in a MySql or Oracle database ...
7
votes
1answer
1k views

Editing tables using SQL in ArcMap [duplicate]

We've recently started working with ArcMap, having used MapInfo previously. I've created a shapefile in ArcCatalog and want to update some columns using SQL. In MapInfo I'd write the following command ...
4
votes
3answers
2k views

Publish SQL Views from Postgres/Postgis to Geoserver

I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver. Does ...
1
vote
2answers
43 views

Viewing results of ST_Difference

I've got an ST_Difference query runnning error free in PostGIS, and in PGAdmin I can see the output table but it's not viewable in QGIS - The table name appears in DB Manager but says "There is no ...
2
votes
0answers
33 views

QGIS - WHERE SQL within a processing.runalg statement for v.surf.rst in python console

I'm creating lots of raster files with grass:v.surf.rst so rather than using the graphical interface I wanted to use the python console (and potentially put in in a script at some point). However ...
11
votes
3answers
4k views

Intersecting a raster with a polygon using PostGIS - artefact error

I'm using PostGIS2.0 to do some raster/polygon intersections. I'm having difficulty understanding which operation I should use, and what the quickest way of performing this is. My problem is as ...
1
vote
1answer
72 views

How to make a SQL query that would sum multiple records in ArcGIS?

I've got a large file geodatabase that contains a polyline feature class of water-pipes. The feature class have many fields, but the ones that I'm interested in is that of the water-pipe's Length and ...
4
votes
3answers
760 views

How to select a number of rows based on current sort order

I would like to be able to select a certain number of rows from my attribute table based on their ordering and not their attributes. For example, if I sort by ObjectID, I then want to select the top ...
6
votes
4answers
1k 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
1answer
26 views

Polygons created for holes after running ST_Collect

I ran an ST_Collect query on a polygon dataset using ogr2gor -dialect sqlite -sql SELECT ST_Collect(geometry),id FROM wl_test GROUP BY id The result is a multipart polygon, which is what I want, ...
0
votes
1answer
35 views

Using MapInfo SQL?

I am trying to create a map of U.S. counties that shows migration patterns. I have a table of all US counties and a table that has migration information. Some counties do not have migration, ...
2
votes
1answer
154 views

How to use ST_DWithin to query features from one table within a distance of a specific feature or features in another table

I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific ...
0
votes
0answers
59 views

PHP, PostGIS & Geojson

I'm using Lumen to connect to postgis and I want to execute this query: WITH query AS (SELECT ST_GeomFromGeoJSON('{"type": "Polygon","coordinates": [[[366063.228612569917459, ...
2
votes
1answer
41 views

GRASS PyWPS Create a new vector map from user selected polygon

My goal is to select a specific polygon in which the user clicked and make a new vector map that contains only the selected polygon. I see two ways: Convert XY coordinate from literal input to a ...
1
vote
0answers
38 views

Inserting search box using dataset column using CartoDB?

I have got some problems to add a search box that can runs searching points from a column of my dataset. I've read this article: How can I link the search box to a dataset column in CartoDB? but ...
0
votes
2answers
193 views

APDM - error exporting database from Oracle

I have a fairly large database on a slow network and I am using a third party tool to update data which calculates other fields and so on. I have a guid string with an empty box (weird glyph - seen ...
0
votes
0answers
10 views

Using predefined variables in whereclause? [duplicate]

Can't seem to figure out how to use variables for an SQL whereclause in python. e.g. want to select_analysis(layer, "new sel", field = attribute) with field and attribute being predefined by user ...
2
votes
1answer
80 views

Esri ArcSDE 10.3.1 Query Inspire Metadata from Oracle (11g) Enterprise GeoDB via SQL

As the title says - I want to create a query in Oracle SQL which will read all dates from the geoprocessing history which can be found using ArcCatalog to show the "Description"-tab from a feature. ...
2
votes
0answers
28 views

Filter features based on attribute percentage threshold [duplicate]

I have a layer (vector layer imported from a Shapefile) in QGIS 2.8.3 with a bunch of features with a single numerical attribute, 'level'. I would like to filter the features with a threshold that is ...
6
votes
2answers
1k views

Using integer variable in where clause of ArcPy script?

I'm trying to include an integer variable in the SQL where-clause of a tool like this (ArcGIS 10 Python script): newR = ExtractByAttributes(inR, '"IntField">=2508') where inR is an integer ...
3
votes
2answers
170 views

subquery in select by attribute in Arcgis

I have a table with several fields. field A contains the same kind of values as does field B. I tried to select all points where the value in field A does not exist in anywhere field B using query ...
1
vote
2answers
53 views

PostGIS insert failed without raising error

I'm learning PostGIS through a book "PostGIS in action". The SQL code written in the book has a lot of issues and I've figured most of them except this one: Copied from the book chapter: CREATE ...
2
votes
2answers
569 views

ArcObjects table views or SQLite3 in Python?

Given a larger csv (1000+ points) with attributes, I need to do both attribute and spatial queries on the data. Would it be faster to read the points into an xy feature class, and do the spatial and ...
1
vote
1answer
26 views

Querying two Separate Columns in ArcPy SQL {where clause}? [duplicate]

Using ArcPy I'm Trying to Set up a Where Clause in my "FeatureClassToFeatureClass_conversion" This {Where Clause} needs to Query Two Columns in the data: FEATURE = 'ENVIRONMENT' and TEMPERATURE ...
1
vote
1answer
35 views

Aggregating taxi data to census block data based on number of taxi trips?

I am doing a study on taxi demand using block level census data and taxi drop off and pick data in the form of Latitude and Longitude around the united states. I am hoping to create a map that shows ...
1
vote
1answer
43 views

Change field names in SQL Server Management Studio

I am building a spatial view using SQL Server Management Studio 2014. One of the tables I am using to build this view has field names that are not compatible with esri; the field names have slashes, ...
0
votes
1answer
68 views

Generating pivot table in PostgreSQL that ties cities to counties using QGIS (or PostGIS)?

I have a cities shapefile and a counties shapefile. I have the counties shapefile imported to a PostgreSQL table. I haven't transferred my cities to PostgreSQL yet, but the issue I really want to ...
1
vote
1answer
36 views

Sum and groupby several conditions

I have a road network where each link is part of a reference number. I am trying to sum up the length of roads per reference number and status. My nested query fails and just sum the total length of ...
1
vote
0answers
48 views

refine nearest_node with radius-distance ( ST_Distance, ST_DWithin )

i am a hobby-IT-person and currently stuck with some simple problem, maybe somebody can help me: This is my working POSTGIS-function, source by underdark: ALTER TABLE distance_graz ADD COLUMN ...
1
vote
1answer
130 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, ...
4
votes
2answers
167 views

How to build QgsExpressions in python?

How can I build a feature request expression using variable inside expression? For example values = [1, 2, 3] for j in range( y ): it = vl.getFeatures( QgsFeatureRequest().setFilterExpression ( ...
2
votes
3answers
194 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
0answers
29 views

How would I build the syntax inside a request in the case I wanted to multiply 2 variables?

I would like add something to my previous question (here: How to build QgsExpressions in python?) How would I build the syntax inside this request in the case I wanted field_name to be also a ...
1
vote
3answers
362 views

How do I find points that are NOT within a list of polygons using MS SQL?

I have a table of points, and and a table of polygons. I have managed to build queries (MS SQL) to get the containing polygon for a given point, as well as listing all the points within a specified ...
0
votes
0answers
34 views

Number of rows returned via `shp2pgsql` for a given shapefile

With the shp2pgsql tool, I see it converts a shapefile into a SQL statement. Upon closer examination, does the SQL generated ALWAYS contains 1 insert statement for 1 row? Or are there instances where ...
2
votes
1answer
1k views

How to fetch polygons from PostGIS into a PHP array?

Currently I fetch polygons from a database table using ST_AsText(). The results are in text format, like this: POLYGON((84 104,212 48,321 122,243 179,275 238,131 240,84 104)) I am not quite sure if ...
0
votes
0answers
178 views

How to improve performance when using a large amount of data (big tables) in ArcGIS?

I'm working with the kinematic wave method to determine the runoff of a basin, I am using the explicit method for resolution (The Lax-Wendroff scheme): I'm working with script code in python (i'm ...
0
votes
1answer
41 views

Using ST_Extrude in Postgres 9.2, with PostGIS 2.2.0

I'm trying to use the function ST_Extrude() in Postgres 9.2. I enabled the PostGIS 2.2.0 extension and the PostGIS_SFCGAL extension (which carries the ST_Extrude function). I can see the function in ...
0
votes
1answer
31 views

How Update one table row with combare with the other polygon table using their geom in SQL Server

a-> points b -> Polygon i want to update city trow in table a with compare b of geom in SQL server this is the PostgreSQL Query update a set city=b.name from b where st_contains(b.the_geom, ...