More

SQL-query in arcpy - SELECT WHERE feature between two values

SQL-query in arcpy - SELECT WHERE feature between two values


I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used".

whereclause = """"X" BETWEEN {0} AND {1}""".format( min(X)[0]-1000, max(X)[0]+1000) ap.FeatureClassToFeatureClass_conversion(SamledeMaalinger,ap.env.workspace,"SamledeMaalingerSort", whereclause)

The whereclause returns "X" BETWEEN 555157.0723 AND 557157.0723 so it's not the string that's the issue.

The SQL-query works if I use < and > instead, but the BETWEEN for some reason doesn't.

So what's the issue? Do the SQL-queries in arcpy have limitations?


As @AlexTereshnekov commented, the problem may be caused by your spatial data storage choice.

I just used the expression below successfully in a file geodatabase feature class:

OID BETWEEN 1 AND 100

but in a shapefile this expression reported that it was invalid SQL:

"FID" BETWEEN 1 AND 100

My testing used ArcGIS 10.2.2 for Desktop


Main difficulty is that you want to have rows for dates for which there is no data in the table. So you have to find a way to generate these rows with zero values.

I think simplest, easiest to understand, solution would be an selectable stored procedure, ie

and to use it you just select from it:

If you want to do without SP then Firebird 2.5 supports recursive CTE which can be used to generate all dates for given range. Use another CTE to calculate sums for dates for which there is data and then join them by date:

Note that in the examples I used column name d instead of date as one can't have column named date in Firebird unless using quoted identifiers (which I never do). Instead of your MyTable I used table name test .

The solutions I can think of are:


As you can see, there are two ways to get things done:

Obviously, second way is much more simple (only two cases against four).

You have to cover all possibilities. From_Date or To_Date could be between your date range or the record dates could cover the whole range.

If one of From_date or To_date is between the dates, or From_date is less than start date and To_date is greater than the end date then this row should be returned.

Try following query to get dates between the range:

This covers all conditions that you are looking for.

Just my 2 cents, I find using the "dd-MMM-yyyy" format safest as the db server will know what you want regardless of the regional settings on the server. Otherwise you could potentially run into issues on a server that has its date regional settings as yyyy-dd-mm (for whatsoever reason)

It's always worked well for me -)

Check this query, i created this query to check whether the check in date over lap with any reservation dates

this will retrun the details which are overlaping , to get the not overlaping details then remove the 'NOT' from the query


Hfrhyu

How do I find out the mythology and history of my Fortress?

Is it fair for a professor to grade us on the possession of past papers?

How to tell that you are a giant?

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

Using audio cues to encourage good posture

How does the secondary effect of the Heat Metal spell interact with a creature resistant/immune to fire damage?

Why is Nikon 1.4g better when Nikon 1.8g is sharper?

Is there hard evidence that the grant peer review system performs significantly better than random?

Is there any word for a place full of confusion?

What order were files/directories outputted in dir?

How can I reduce the gap between left and right of cdot with a macro?

Maximum summed subsequences with non-adjacent items

Morning, Afternoon, Night Kanji

Most bit efficient text communication method?

Do I really need to have a message in a novel to appeal to readers?

ArcGIS Pro Python arcpy.CreatePersonalGDB_management

Is a ledger board required if the side of my house is wood?

How much damage would a cupful of neutron star matter do to the Earth?

How to react to hostile behavior from a senior developer?

Can the Great Weapon Master feat's damage bonus and accuracy penalty apply to attacks from the Spiritual Weapon spell?

Is grep documentation about ignoring case wrong, since it doesn't ignore case in filenames?

Selecting and updating attribute values using Python console in QGIS?

Unicorn Meta Zoo #1: Why another podcast?“Select by Attribute” in QGIS using python?QGIS plugin select and edit feature attributeUpdating an Open Attribute TableUpdating feature attribute in QGIS plugin?How to get QgsFeatureId with row and column in qtablewidget.(PyQGIS)Feature Selection Using Custom Python Expression Functions in QGIS 2.18QGIS: SQL query within python console to sum a column based on values of other two columnsCannot calculate new values for an empty attribute using existing attributes in QGIS with pythonSelect features using an expression, QGISChange attribute table value using graphical modeler?

I am working with Landusefc and Class columns (ref table).

What I want is: Selecting all water from "Class" and giving a value say '8' in the "Landusefc" column for these selected features.


I have tried the following code, it works till the selection part but doesn't seem to change the attribute value using the ChangeAttributeValue .

I am working with Landusefc and Class columns (ref table).

What I want is: Selecting all water from "Class" and giving a value say '8' in the "Landusefc" column for these selected features.


I have tried the following code, it works till the selection part but doesn't seem to change the attribute value using the ChangeAttributeValue .

I am working with Landusefc and Class columns (ref table).

What I want is: Selecting all water from "Class" and giving a value say '8' in the "Landusefc" column for these selected features.


I have tried the following code, it works till the selection part but doesn't seem to change the attribute value using the ChangeAttributeValue .

I am working with Landusefc and Class columns (ref table).

What I want is: Selecting all water from "Class" and giving a value say '8' in the "Landusefc" column for these selected features.


I have tried the following code, it works till the selection part but doesn't seem to change the attribute value using the ChangeAttributeValue .


Syntax

The table containing the field that will be updated with the new calculation.

The field that will be updated with the new calculation.

The simple calculation expression used to create a value that will populate the selected rows.

Specifies the type of expression that will be used.

  • VB —The expression will be written in a standard VB format. This is the default.
  • PYTHON —The expression will be written in a standard Python format. Use of geoprocessor methods and properties is the same as creating a 9.2 version geoprocessor.
  • PYTHON_9.3 —The expression will be written in a standard Python format. Use of geoprocessor methods and properties is the same as creating a 9.3 version geoprocessor.
Caution:

Field calculations with a VB Expression type are not supported on 64-bit products, including ArcGIS Pro , ArcGIS Desktop — Background Geoprocessing (64-bit) —and ArcGIS Server . To successfully use Calculate Field in these products, expressions should be converted to Python, or in the case of Background Geoprocessing (64-bit) , background processing can alternatively be disabled.

A block of code that will be entered for complex expressions.

Derived Output


How to Calculate Distance Between Two Points in SQL Server 2008 Spatial Data (Geography)

Microsoft SQL Server 2008 introduced Spatial Data to SQL developers.
Spatial data in Microsoft SQL Server enables sql programmers to use Geography and Geometry data types.
The easiest way to find distance between two points (Geography Point data type) is using Geography data type STDistance method.

Here is a t-sql spatial sample which is calculating distance between two points on earth surface.
Since we are dealing with points on earth, then we are required to work with geography data types.

The default Spatial Reference Id (SRID) for Geography types is 4326 in Microsoft SQL Server.
So SRID 4326 is not used in the t-sql example of Spatial Geography Point representation.

Please note that Geography STDistance() method return values are in units that the spatial reference system used for related geography data.
So in our case using the Microsoft SQL Server default SRID, the Geography STDistance() method output value is in meters unit for SRID 4326.
Although the latitude and longitude values are in degrees, the unit of measure of distance between two points is meters.

For detailed information about which SRID or spatial reference system uses which unit of measure, please check the system view sys.spatial_reference_systems

Here is another sql spatial distance calculation using Geography STDistance method.
Here the Geography Point variables are declared in a different way.
But the method used to find distance between two points is same : STDistance() method.


TOP 1 without ORDER BY could not be guaranteed to return the same results even if the output of STRING_SPLIT() were guaranteed (which it most definitely is not). While in real-world usage you may find it hard to come up with a counter-example where the data is not returned in order, this is a terrible kind of thing to do. Trusting that something always works because you've never seen it break is like assuming that if you put a deer sign on the highway that is the only place you'll ever see a deer.

However, let's look at another way we could solve this. Why don't we locate the position of each individual string in the overall list:

Do you think we could use this to determine the first element in the list? Of course! Let's try again:

You could use the same logic to find the last element, just change the ORDER BY idx to ORDER BY idx DESC . In fact, you could use this logic to return the nth string in the list:

As a disclaimer, if you have duplicates, that's going to mess things up, because the index value will always represent the first appearance of that value in the string. You can switch from ROW_NUMBER() to DENSE_RANK() but that won't solve for all cases. You can de-dupe the string first (I talk about some edge cases here).


Using PowerShell to discover information about your Microsoft SQL Servers

I’m an infrastructure guy who supports many different products at multiple datacenters in an enterprise environment. One of those products is Microsoft SQL Server which I’ve been supporting since version 6.5 back in the 1990s. In this article, I’ll be discussing how PowerShell can be used to retrieve just about any information that you would want to know about modern versions of SQL Server that you currently have running in your environment. This article isn’t meant to be a deep dive, it’s meant to get you started thinking about how you could write your own PowerShell code to retrieve the specific information that you’re looking for from your SQL Servers.

In this scenario, you have two servers running the core installation (no-GUI) version of Windows Server 2012 R2. One has SQL Server 2008 R2 installed and the other one has SQL Server 2014 installed, each one has multiple instances of SQL Server installed. All of the examples shown are being performed on a Windows 8.1 Enterprise edition workstation with the SQL Server 2014 management tools installed and the RSAT (Remote Server Administration Tools) installed.

Note: SQL Server 2008 R2 and prior versions of SQL Server that supported PowerShell use a snap-in and beginning with SQL Server 2012 a module is used instead.

Working with SQL Server as if it were a file system

One way of working with SQL Server in PowerShell is through the SQLServer PSDrive that’s created when the SQL PowerShell snap-in or module is imported. This allows you to work with SQL Server as if it were a file system.

Since the SQL Server 2014 client tools are installed on our workstation, there is a PowerShell module named SQLPS installed and you’ll need to start out by importing that module:

Notice that the -DisableNameChecking parameter was specified in the previous example. There are a couple of cmdlets in the SQLPS module that use unapproved verbs (Encode-SqlName and Decode-SqlName) that will cause warnings to be generated if this optional parameter isn’t specified. The warnings wouldn’t hurt anything, but I prefer not to see them. You can also see in the previous example that when the SQLPS module is imported, it automatically changes your current location to the SQLSERVER PSDrive.

Determining the instances for the server named SQL01 is simple as shown in the following example:

One of the things that makes PowerShell so powerful is that once you figure out how to perform a task for one item (one computer in this scenario), it’s easy to perform that same task for multiple items.

The ForEach-Object cmdlet can be used to return the results for multiple SQL Servers:

By default, only the InstanceName property is returned, so you have no idea which instances belong to which servers.

Just like any other cmdlet that produces output, you can pipe the previous command to Get-Member to see all of the available properties or Format-List –Properties * to see all of the available properties and their values. Be prepared to be overwhelmed though because there’s a lot more information about SQL Server that can be obtained. For the sake of simplicity, I chose to focus on the Instance Name so here are a few helpful properties:

&lsquoSQL01&rsquo, &lsquoSQL02&rsquo | ForEach-Object

Creating or constructing a new geometry instance

Creating a New geometry Instance from an Existing Instance

The geometry data type provides numerous built-in methods you can use to create new geometry instances based on existing instances.

To create a buffer around a geometry
STBuffer (geometry Data Type)

To create a simplified version of a geometry
Reduce (geometry Data Type)

To create the convex hull of a geometry
STConvexHull (geometry Data Type)

To create a geometry from the intersection of two geometries
STIntersection (geometry Data Type)

To create a geometry from the union of two geometries
STUnion (geometry Data Type)

To create a geometry from the points where one geometry does not overlap another
STDifference (geometry Data Type)

To create a geometry from the points where two geometries do not overlap
STSymDifference (geometry Data Type)

To create an arbitrary Point instance that lies on an existing geometry
STPointOnSurface (geometry Data Type)

Constructing a geometry Instance from Well-Known Text Input

The geometry data type provides several built-in methods that generate a geometry from the Open Geospatial Consortium (OGC) WKT representation. The WKT standard is a text string that allows geometry data to be exchanged in textual form.

To construct any type of geometry instance from WKT input
STGeomFromText (geometry Data Type)

To construct a geometry Point instance from WKT input
STPointFromText (geometry Data Type)

To construct a geometry MultiPoint instance from WKT input
STMPointFromText (geometry Data Type)

To construct a geometry LineString instance from WKT input
STLineFromText (geometry Data Type)

To construct a geometry MultiLineString instance from WKT input
STMLineFromText (geometry Data Type)

To construct a geometry Polygon instance from WKT input
STPolyFromText (geometry Data Type)

To construct a geometry MultiPolygon instance from WKT input
STMPolyFromText (geometry Data Type)

To construct a geometry GeometryCollection instance from WKT input
STGeomCollFromText (geometry Data Type)

Constructing a geometry Instance from Well-Known Binary Input

WKB is a binary format specified by the Open Geospatial Consortium (OGC) that permits geometry data to be exchanged between a client application and an SQL database. The following functions accept WKB input to construct geometries:

To construct any type of geometry instance from WKB input
STGeomFromWKB (geometry Data Type)

To construct a geometry Point instance from WKB input
STPointFromWKB (geometry Data Type)

To construct a geometry MultiPoint instance from WKB input
STMPointFromWKB (geometry Data Type)

To construct a geometry LineString instance from WKB input
STLineFromWKB (geometry Data Type)

To construct a geometry MultiLineString instance from WKB input
STMLineFromWKB (geometry Data Type)

To construct a geometry Polygon instance from WKB input
STPolyFromWKB (geometry Data Type)

To construct a geometry MultiPolygon instance from WKB input
STMPolyFromWKB (geometry Data Type)

To construct a geometry GeometryCollection instance from WKB input
STGeomCollFromWKB (geometry Data Type)

Constructing a geometry Instance from GML Text Input

The geometry data type provides a method that generates a geometry instance from GML, an XML representation of geometric objects. SQL Server supports a subset of GML.

To construct any type of geometry instance from GML input
GeomFromGml (geometry Data Type)


When you query the INFORMATION_SCHEMA.VIEWS view, the query results contain one row for each view in a dataset.

The INFORMATION_SCHEMA.VIEWS view has the following schema:

Column name Data type Value
TABLE_CATALOG STRING The name of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the view also referred to as the dataset id
TABLE_NAME STRING The name of the view also referred to as the table id
VIEW_DEFINITION STRING The SQL query that defines the view
CHECK_OPTION STRING The value returned is always NULL
USE_STANDARD_SQL STRING YES if the view was created by using a standard SQL query NO if useLegacySql is set to true

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.VIEWS view except for check_option which is reserved for future use. The metadata returned is for all views in mydataset in your default project &mdash myproject .

To run the query against a project other than your default project, add the project ID to the dataset in the following format: ` project_id `. dataset .INFORMATION_SCHEMA. view for example, `myproject`.mydataset.INFORMATION_SCHEMA.VIEWS .

Console

Open the BigQuery page in the Cloud Console.

Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

The results should look like the following:

Note that the results show that this view was created by using a legacy SQL query.

Example 2:

The following example retrieves the SQL query and query syntax used to define myview in mydataset in your default project &mdash myproject .

To run the query against a project other than your default project, add the project ID to the dataset in the following format: ` project_id `. dataset .INFORMATION_SCHEMA. view for example, `myproject`.mydataset.INFORMATION_SCHEMA.VIEWS .

Console

Open the BigQuery page in the Cloud Console.

Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

The results should look like the following:

Note that the results show that this view was created by using a standard SQL query.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.


Watch the video: MS Access - jednostavni select upiti