More

Why does my query not run since SRID are the same?

Why does my query not run since SRID are the same?


I am running this query:

SELECT source,ST_AsText(ST_ClosestPoint(roads.geom,ST_GeomFromText('POINT(22.256 39.569)'))) FROM roads ORDER BY ST_Distance(roads.geom,ST_GeomFromText('POINT(22.256 39.569)')')) LIMIT 1;

I have followed this answer and reprojected my table geom_ways in my database to 4326 which is the SRID of geometry points but the query will not run because there are not matching geometries?


ST_GeomFromText('POINT(22.256 39.569)')is not going to give you a geometry in EPSG:4326.

From the documentation:

There are 2 variants of ST_GeomFromText function, the first takes no SRID and returns a geometry with no defined spatial reference system. The second takes a spatial reference id as the second argument and returns an ST_Geometry that includes this srid as part of its meta-data. The srid must be defined in the spatial_ref_sys table.

So you probably want to do:ST_GeomFromText('POINT(22.256 39.569)', 4326)

Basically, WKT doesn't have the notion of a spatial reference.

You can also generate a specific SRID using EWKT, but the two-argument form is usually easier. In EWKT it would be:ST_GeomFromEWKT('SRID=4326;POINT(22.256 39.569)')


Your .GroupBy(y => y.LanguageCode).ToDictionaryAsync(y => y.Key, y => y.Select(z => z.Name)) cannot be converted to SQL. EF Core 3.0 will throw exception to make sure you know that all records in Units will be fetched from database before grouping and map to Dictionary.

It seems like there is a common misconception about what LINQ GroupBy does and what SQL GROUP BY is able to do. Since I fell into the exact same trap and had to wrap my head around this recently, I decided to write a more thorough explanation of this issue.

Short answer:

The LINQ GroupBy is much different from the SQL GROUP BY statement: LINQ just divides the underlying collection into chunks depending on a key, while SQL additionally applies an aggregation function to condense each of these chunks down into a single value.

This is why EF has to perform your LINQ-kind GroupBy in memory.

Before EF Core 3.0, this was done implicitly, so EF downloaded all result rows and then applied the LINQ GroupBy . However, this implicit behavior might let the programmer expect that the entire LINQ query is executed in SQL, with potentially enormous performance impact when the result set is rather large. For this reason, implicit client side evaluation of GroupBy was disabled completely in EF Core 3.0.

Now it is required to explicitly call functions like .AsEnumerable() or .ToList() , which download the result set and continue with in-memory LINQ operations.

Long answer:

The following table solvedExercises will be the running example for this answer:

A record X | Y in this table denotes that student X has solved exercise Y .

In the question, a common use case of LINQ's GroupBy method is described: Take a collection and group it into chunks, where the rows in each chunk share a common key.

In our example, we might want to get a Dictionary<int, List<int>> , which contains a list of solved exercises for each student. With LINQ, this is very straightforward:

This is easy to represent with C# datatypes, since we can nest List and Dictionary as deep as we like to.

Now we try to imagine this as an SQL query result. SQL query results are usually represented as a table, where we can freely choose the returned columns. To represent our above query as SQL query result, we would need to

  • generate multiple result tables,
  • put the grouped rows into an array or
  • somehow insert a "result set separator".

As far as I know, none of these approaches is implemented in practice. At most, there are some hacky work-arounds like MySQL's GROUP_CONCAT , which allows to combine the result rows into a string (relevant SO answer).

Thus we see, that SQL cannot yield results that match LINQ's notion of GroupBy .

Instead, SQL only allows so-called aggregation: If we, for example, wanted to count how many exercises have been passed by a student, we would write

Aggregation functions reduce a set of rows into a single value, usually a scalar. Examples are row count, sum, maximum value, minimum value, and average.

This is implemented by EF Core: Executing

generates the above SQL. Note the Select , which tells EF which aggregation function it should use for the generated SQL query.

In summary, the LINQ GroupBy function is much more general than the SQL GROUP BY statement, which due to SQL's restrictions only allows to return a single, two dimensional result table. Thus, queries like the one in the question and the first example in this answer have to be evaluated in memory, after downloading the SQL result set.

Instead of implicitly doing this, in EF Core 3.0 the developers chose to throw an exception in this case this prevents accidental downloading of an entire, potentially large table with millions of rows, which might get unnoticed during development due to a small test database.


Geospatial data support in OData

This is a strawman proposal. Please challenge it in the OData mailing list.

OData supports geospatial data types as a new set of primitives. They can be used just like any other primitives - passed in URLs as literals, as types and values for properties, projected in $select, and so on. Like other primitives, there is a set of canonical functions that can be used with them.

Why are we leaning towards this design?

Boxes like these question the design and provide the reasoning behind the choices so far.

This is currently a living document. As we continue to make decisions, we will adjust this document. We will also record our new reasons for our new decisions.

The only restriction, as compared to other primitives, is that geospatial types may not be used as entity keys (see below).

The rest of this spec goes into more detail about the geospatial type system that we support, how geospatial types are represented in $metadata, how their values are represented in Atom and JSON payloads, how they are represented in URLs, and what canonical functions are defined for them.

Modeling

Primitive Types

Our type system is firmly rooted in the OGC Simple Features geometry type system. We diverge from their type system in only four ways.


Figure 1: The OGC Simple Features Type Hierarchy

Why a subset?

Our primary goal with OData is to get everyone into the ecosystem. Thus, we want to make it easy for people to start. Reducing the number of types and operations makes it easier for people to get started. There are extensibility points for those with more complex needs.

First, we expose a subset of the type system and a subset of the operations. For details, see the sections below.

Second, the OGC type system is defined for only 2-dimensional geospatial data. We extend the definition of a position to be able to handle a larger number of dimensions. In particular, we handle 2d, 3dz, 3dm, and 4d geospatial data. See the section on Coordinate Reference Systems (CRS) for more information.

Why separate Geometrical and Geographical types?

They actually behave differently. Assume that you are writing an application to track airplanes and identify when their flight paths intersect, to make sure planes don't crash into each other.

Assume you have two flight plans. One flies North, from (0, 0) to (0, 60). The other flies East, from (-50, 58) to (50, 58). Do they intersect?

In geometric coordinates they clearly do. In geographic coordinates, assuming these are latitude and longitude, they do not.

That's because geographic coordinates are on a sphere. Airplanes actually travel in arcs. The eastward-flying plane actually takes a path that bends up above the flight path of the northward plane. These planes miss each other by hundreds of miles.

Obviously, we want our crash detector to know that these planes are safe.

The two types may have the same functions, but they can have very different implementations. Splitting these into two types makes it easier for function implementers. They don't need to check the CRS in order to pick their algorithm.

Third, the OGC type system ised for flat-earth geospatial data (termed geometrical data hereafter). OGC does not define a system that handles round-earth geospatial data (termed geographical data). Thus, we duplicate the OGC type system to make a parallel set of types for geographic data.

We refer to the geographical vs geometrical distinction as the topology of the type. It describes the shape of the space that includes that value.

Some minor changes in representation are necessary because geographic data is in a bounded surface (the spheroid), while geometric data is in an infinite surface (the plane). This shows up, for example, in the definition of a Polygon. We make as few changes as possible see below for details. Even when we make changes, we follow prior art wherever possible.

Finally, like other primitives in OData, the geospatial primitives do not use inheritance and are immutable. The lack of inheritance, and the subsetting of the OGC type system, give us a difficulty with representing some data. We resolve this with a union type that behaves much like the OGC's base class. See the Union types section below for more details.

Coordinate Reference Systems

Although we support many Coordinate Reference Systems (CRS), there are several limitations (as compared to the OGC standard):

  • We only support named CRSated by an SRID. This should be an official SRID. In particular, we don't support custom CRS defined in the metadata, as does GML.
    • Thus, some data will be inexpressible. For example, there are hydrodynamics readings data represented in a coordinate system where each point has coordinates [lat, long, depth, time, pressure, temperature]. This lets them do all sorts of cool analysis (eg, spatial queries across a surface defined in terms of the temperature and time axes), but is beyond the scope of OData.
    • There are also some standard coordinate systems that don't have codes. So we couldn't represent those. Ex: some common systems in New Zealand & northern Europe.
    • The CRS is static under projection. The above holds even between results of a projection.
    • There is a single "undefined" SRID value. This allows a service to explicitly state that the CRS varies on a per-instance basis.
    • There are client-side libraries that can do some coordinate transforms for you.
    • Servers could expose coordinate transform functions as non-OGC canonical function extensions. See below for details.

    Nominally, the Geometry/Geography type distinction is redundant with the CRS. Each CRS is inherently either round-earth or flat-earth. However, we are not going to automatically resolve this. Our implementation will not know which CRS match which model type. The modeler will have to specify both the type & the CRS.

    There is a useful default CRS for Geography (round earth) data: WGS84. We will use that default if none is provided.

    The default CRS for Geometry (flat earth) data is SRID 0. This represents an arbitrary flat plane with unit-less dimensions.

    The Point types - Edm.Point and Edm.GeometricPoint

    Why the bias towards the geographic types?

    Mobile devices are happening now. A tremendous amount of new data and new applications will be based on the prevalence of these devices. They all use WGS84 for their spatial data.

    Mobile devide developers also tend to be more likely to try to copy some code from a blog or just follow intellisense until something works. Hard-core developers are more likely to read the docs and think things through. So we want to make the obvious path match the mobile developers.

    "Point" is defined as per the OGC. Roughly, it consists of a single position in the underlying topology and CRS. Edm.Point is used for points in the round-earth (geographic) topology. Edm.GeometricPoint is a point in a flat-earth (geometric) topology.

    These primitives are used for properties with a static point type. All entities of this type will have a point value for this property.

    Example properties that would be of type point or geometric point include a user's current location or the location of a bus stop.

    The LineString types - Edm.LineString and Edm.GeometricLineString

    "LineString" is defined as per the OGC. Roughly, it consists of a set of positions with linear interpolation between those positions, all in the same topology and CRS, and represents a path. Edm.LineString is used for geographic LineStrings Edm.GeometricLineString is used for geometric ones.

    These primitives are used for properties with a static path type. Example properties would be the path for a bus route entity, or the path that I followed on my jog this morning (stored in a Run entity).

    The Polygon types - Edm.Polygon and Edm.GeometricPolygon

    "Polygon" is defined as per the OGC. Roughly, it consists of a single bounded area which may contain holes. It is represented using a set of LineStrings that follow specific rules. These rules differ for geometric and geographic topologies.

    These primitives are used for properties with a static single-polygon type. Examples include the area enclosed in a single census tract, or the area reachable by driving for a given amount of time from a given initial point.

    Some things that people think of as polygons, such as the boundaries of states, are not actually polygons. For example, the state of Hawaii includes several islands, each of which is a full bounded polygon. Thus, the state as a whole cannot be represented as a single polygon. It is a Multipolygon, and can be represented in OData only with the base types.

    The base types - Edm.Geography and Edm.Geometry

    The base type represents geospatial data of an undefined type. It might vary per entity. For example, one entity might hold a point, while another holds a multi-linestring. It can hold any of the types in the OGC hierarchy that have the correct topology and CRS.

    Although core OData does not support any functions on the base type, a particular implementation can support operations via extensions (see below). In core OData, you can read & write properties that have the base types, though you cannot usefully filter or order by them.

    The base type is also used for dynamic properties on open types. Because these properties lack metadata, the server cannot state a more specific type. The representation for a dynamic property MUST contain the CRS and topology for that instance, so that the client knows how to use it.

    Therefore, spatial dynamic properties cannot be used in $filter, $orderby, and the like without extensions. The base type does not expose any canonical functions, and spatial dynamic properties are always the base type.

    Edm.Geography represents any value in a geographic topology and given CRS. Edm.Geometry represents any value in a geometric topology and given CRS.

    Each instance of the base type has a specific type that matches an instantiable type from the OGC hierarchy. The representation for an instance makes clear the actual type of that instance.

    Thus, there are no instances of the base type. It is simply a way for the $metadata to state that the actual data can vary per entity, and the client should look there.

    Spatial Properties on Entities

    Zero or more properties in an entity can have a spatial type. The spatial types are regular primitives. All the standard rules apply. In particular, they cannot be shredded under projection. This means that you cannot, for example, use $select to try to pull out the first control position of a LineString as a Point.

    For open types, the dynamic properties will all effectively be of the union type. You can tell the specific type for any given instance, just as for the union type. However, there is no static type info available. This means that dynamic properties need to include the CRS & topology.

    Spatial-Primary Entities (Features)

    This is a non-goal. We do not think we need these as an intrinsic. We believe that we can model this with a pass-through service using vocabularies.

    Communicating

    Metadata

    We define new types: Edm.Geography, Edm.Geometry, Edm.Point, Edm.GeometricPoint, Edm.Polygon, Edm.GeometricPolygon. Each of them has a facet that is the CRS, called "coordinate_system".

    Entities in Atom

    What should we use?

    To spark discussion, and because it is perhaps the best of a bad lot, the strawman proposal is to use the same GML profile as Sql Server uses. This is an admittedly hacked-down simplification of full GML, but seems to match the domain reasonably well.

    Here are several other options, and some of the problems with each:

    GeoRSS only supports some of the types.

    Full GML supports way too much - and is complex because of it.

    KML ised for spatial data that may contain embedded non-spatial data. This allows creating data that you can't then use OData to easily query. We would prefer that people use full OData entities to express this metadata, so that it can be used by clients that do not support geospatial data.

    Another option would be an extended WKT. This isn't XML. This isn't a problem for us, but may annoy other implementers(?). More critically, WKT doesn't support 3d or 4d positions. We need those in order to support full save & load for existing data. The various extensions all disagree on how to extend for additional dimensions. I'd prefer not to bet on any one WKT implementation, so that we can wait for another standards body to pick the right one.

    PostGIS does not seem to have a native XML format. They use their EWKT.

    Finally, there's the SqlServer GML profile. It is a valid GML profile, and isn't quite as much more than we need as is full GML. I resist it mostly because it is a Microsoft format. Of course, if there is no universal format, then perhaps a Microsoft one is as good as we can get.

    Entities in JSON

    Why GeoJSON?

    It flows well in a JSON entity, and is reasonably parsimonious. It is also capable of supporting all of our types and coordinate systems.

    It is, however, not an official standard. Thus, we may have to include it by copy, rather than by reference, in our official standard.

    Another option is to use ESRI's standard for geospatial data in JSON. Both are open standards with existing ecosystems. Both seem sufficient to our needs. Anyone have a good reason to choose one over the other?

    We will use GeoJSON. Technically, since GeoJSON ised to support entire geometry trees, we are only using a subset of GeoJSON. We do not allow the use of types "Feature" or "FeatureCollection." Use entities to correlate a geospatial type with metadata.

    Why the ordering constraint?

    This lets us distinguish a GeoJSON primitive from a complex type without using metadata. That allows clients to parse a JSON entity even if they do not have access to the metadata.

    This is still not completely unambiguous. Another option would be to recommend an "__type" member as well as a "type" member. The primitive would still be valid GeoJSON, but could be uniquely distinguished during parsing.

    We believe the ordering constraint is lower impact.

    Furthermore, "type" SHOULD be ordered first in the GeoJSON object, followed by coordinates, then the optional properties.

    Dynamic Properties

    Geospatial values in dynamic properties are represented exactly as they would be for static properties, with one exception: the CRS is required. The client will not be able to examine metadata to find this value, so the value must specify it.

    Querying

    Geospatial Literals in URIs

    Why only 2d?

    Because OGC only standardized 2d, different implementations differ on how they extended to support 3dz, 3dm, and 4d. We may add support for higher dimensions when they stabilize. As an example, here are three different representations for the same 3dm point:

    • PostGIS: POINTM(1, 2, 3)
    • Sql Server: POINT(1, 2, NULL, 3)
    • ESRI: POINT M (1, 2, 3)

    The standard will probably eventually settle near the PostGIS or ESRI version, but it makes sense to wait and see. The cost of choosing the wrong one is very high: we would split our ecosystem in two, or be non-standard.

    There are at least 3 common extensions to WKT (PostGIS, ESRI, and Sql Server), but all use the same extension to include an SRID. As such, they all use the same representation for values with 2d coordinates. Here are some examples:

    If OData were to support 3dm, then that last one could be exposed and used something like one of (depending on which standard we go with):

    Why not GeoJSON?

    GeoJSON actually makes a lot of sense. It would reduce the number of formats used in the standard by 1, making it easier for people to add geospatial support to service endpoints. We are also considering using JSON to represent entity literals used with Functions. Finally, it would enable support for more than 2 dimensions.

    However, JSON has a lot of nesting brackets, and they are prominent in GeoJSON. This is fine in document bodies, where you can use linebreaks to make them readable. However, it is a problem in URLs. Observe the following example (EWKT representation is above, for comparison):

    Not usable everywhere

    Why not?

    Geospatial values are neither equality comparable nor partially-ordered. Therefore, the results of these operations would be undefined.

    Furthermore, geospatial types have very long literal representations. This would make it difficult to read a simple URL that navigates along a series of entities with geospatial keys.

    If your entity concurrency control needs to incorporate changes to geospatial properties, then you should probably use some sort of Entity-level version tracking.

    Geospatial primitives MAY NOT be compared using lt , eq , or similar comparison operators.

    Geospatial primitives MAY NOT be used as keys.

    Geospatial primitives MAY NOT be used as part of an entity's ETag.

    Distance Literals in URLs

    Some queries, such as the coffee shop search above, need to represent a distance.

    Distance is represented the same in the two topologies, but interpreted differently. In each case, it is represented as a float scalar. The units are interpreted by the topology and coordinate system for the property with which it is compared or calculated.

    Because a plane is uniform, we can simply define distances in geometric coordinates to be in terms of that coordinate system's units. This works as long as each axis uses the same unit for its coordinates, which is the general case.

    Geographic topologies are not necessarily uniform. The distance between longitude -125 and -124 is not the same at all points on the globe. It goes to 0 at the poles. Similarly, the distance between 30 and 31 degrees of latitude is not the same as the distance between 30 and 31 degrees of longitude (at least, not everywhere). Thus, the underlying coordinate system measures position well, but does not work for describing a distance.

    For this reason, each geographic CRS also defines a unit that will be used for distances. For most CRSs, this is meters. However, some use US feet, Indian feet, German meters, or other units. In order to determine the meaning of a distance scalar, the developer must read the reference for the CRS involved.

    New Canonical Functions

    Each of these canonical functions is defined on certain geospatial types. Thus, each geospatial primitive type has a set of corresponding canonical functions. An OData implementation that supports a given geospatial primitive type SHOULD support using the corresponding canonical functions in $filter. It MAY support using the corresponding canonical functions in $orderby.

    Are these the right names?

    We might consider defining these canonical functions as Geo.distance, etc. That way, individual server extensions for standard OGC functions would feel like core OData. This works as long as we explicitly state (or reference) the set of functions allowed in Geo.

    Distance

    Distance is a canonical function defined between points. It returns a distance, as defined above. The two arguments must use the same topology & CRS. The distance is measured in that topology. Distance is one of the corresponding functions for points. Distance is defined as equivalent to the OGC ST_Distance method for their overlapping domain, with equivalent semantics for geographical points.

    Intersects

    Intersects identifies whether a point is contained within the enclosed space of a polygon. Both arguments must be of the same topology & CRS. It returns a Boolean value. Intersects is a corresponding function for any implementation that includes both points and polygons. Intersects is equivalent to OGC's ST_Intersects in their area of overlap, extended with the same semantics for geographic data.

    Length

    Length returns the total path length of a linestring. It returns a distance, as defined above. Length is a corresponding function for linestrings. Length is equivalent to the OGC ST_Length operation for geometric linestrings, and is extended with equivalent semantics to geographic data.

    Why this subset?

    It matches the two most common scenarios: find all the interesting entities near me, and find all the interesting entities within a particular region (such as a viewport or an area a use draws on a map).

    Technically, linestrings and length are not needed for these scenarios. We kept them because the spec felt jagged without them.

    All other OGC functions

    We don't support these, because we want to make it easier to stand up a server that is not backed by a database. Some are very hard to implement, especially in geographic coordinates.

    A provider that is capable of handling OGC Simple Features functions MAY expose those as Functions on the appropriate geospatial primitives (using the new Function support).

    We are reserving a namespace, " Geo ," for these standard functions. If the function matches a function specified in Simple Features, you SHOULD place it in this namespace. If the function does not meet the OGC spec, you MUST NOT place it in this namespace. Future versions of the OData spec may define more canonical functions in this namespace. The namespace is reserved to allow exactly these types of extensions without breaking existing implementations.

    In the SQL version of the Simple Features standard, the function names all start with ST_ as a way to provide namespacing. Because OData has real namespaces, it does not need this pseudo-namespace. Thus, the name SHOULD NOT include the ST_ when placed in the Geo namespace. Similarly, the name SHOULD be translated to lowercase, to match other canonical functions in OData. For example, OGC's ST_Buffer would be exposed in OData as Geo.buffer . This is similar to the Simple Features implementation on CORBA.

    All other geospatial functions

    Any other geospatial operations MAY be exposed by using Functions. These functions are not defined in any way by this portion of the spec. See the section on Functions for more information, including namespacing issues.

    Examples

    Find coffee shops near me

    Find the nearest 3 coffee shops, by drive time

    This is not directly supported by OData. However, it can be handled by an extension. For example:

    Note that while distanceto is symmetric in its args, MyNamespace.driving_time_to might not be. For example, it might take one-way streets into account. This would be up to the data service that is defining the function.

    Compute distance along routes

    Find all houses close enough to work

    For this example, let's assume that there's one OData service that can tell you the drive time polygons around a point (via a service operation). There's another OData service that can search for houses. You want to mash them up to find you houses in your price range from which you can get to work in 15 minutes.

    Then, you'd send the actual search query to the second endpoint:

    Is there any way to make that URL shorter? And perhaps do this in one query?

    This is actually an overly-simple polygon for a case like this. This is just a square with a single hole in it. A real driving polygon would contain multiple holes and a lot more boundary points. So that polygon in the final query would realistically be 3-5 times as long in the URL.

    It would be really nice to support reference arguments in URLs (with cross-domain support). Then you could represent the entire example in a single query:

    However, this is not supported in OData today.

    OK, but isn't there some other way to make the URL shorter? Some servers can't handle this length!

    We are looking at options. The goal is to maintain GET-ability and cache-ability. A change in the parameters should be visible in the URI, so that caching works correctly.

    The current front-runner idea is to allow the client to place parameter values into a header. That header contains a JSON dictionary of name/value pairs. If it does so, then it must place the hashcode for that dictionary in the query string. The resulting request looks like:

    Of course, nothing about this format is at all decided. For example, should that header value be fully in JSON (using the same formatting as in a JSON payload)? Should it be form encoded instead of JSON? Perhaps the entire query string should go in the header, with only a $query=HASHCODE in the URL? And there are a lot more options.


    Creating a private saved query

    When you save a query and set the visibility to Private, the query can be viewed, updated, or deleted only by you.

    To create a private shared query:

    Console

    In the Cloud Console, open the BigQuery page.

    Click Compose new query.

    Enter a valid SQL query in the Query editor text area. For example, you can use the query from the Cloud Console quickstart.

    Click Save query.

    In the Save query dialog, enter a name for your query, set Visibility to Personal (editable only by you), and then click Save.

    To view your saved query, click Saved Queries. The Personal queries tab opens by default.


    When you perform an DOM query through jQuery like $('class-name') it actively searched the DOM for that element and returns that element with all the jQuery prototype methods attached.

    When you're within the jQuery chain or event you don't have to rerun the DOM query you can use the context $(this) . Like so:

    $(this) will hold the element that you originally requested. It will attach all the jQuery prototype methods again, but will not have to search the DOM again.

    Quote from a web blog that doesn't exist anymore but I'll leave it in here for history sake:

    In my opinion, one of the best jQuery performance tips is to minimize your use of jQuery. That is, find a balance between using jQuery and plain ol’ JavaScript, and a good place to start is with ‘this‘. Many developers use $(this) exclusively as their hammer inside callbacks and forget about this, but the difference is distinct:

    When inside a jQuery method’s anonymous callback function, this is a reference to the current DOM element. $(this) turns this into a jQuery object and exposes jQuery’s methods. A jQuery object is nothing more than a beefed-up array of DOM elements.


    3 Answers 3

    To answer your first question I'd like to hint you to --query :

    Armed with this you will get the link source, e.g. in my case for java:

    So, you see my chain goes like /usr/bin/java -> /etc/alternatives/java -> . .

    Unfortunately, I don't have an answer to your second question. I've been searching for a solution for this some time ago, but it seems that there is no simple workaround, yet. What you could do is writing some hack to parse the output of the --query call, maybe like this:

    which you could then use as input for the --install call. But since this is quite messy in my opinion I won't recommend it. Instead you might want to have a look at galternatives , a front-end to the alternatives system. Even if I don't like to use graphical stuff for such basic jobs it is quite convenient and I ended up using this tool instead of the command line tools.

    I've been curious how update-alternatives knows what's the command symlink and took a short look into the sources. Of course the alternatives system has to store the config for each group and it turns out that it's called administrative directory and written on the man page -)

    You'll find this information in /var/lib/dpkg/alternatives (by default). The second line in each of these files defines the master link you're looking for. You may extract it like this:

    However, this is just a workaround for those having an older version of update-alternatives .


    Traffic View

    What does Traffic View do?

    Traffic View is a feature of Traffic Manager that helps you understand more about your users and how their experience is. It uses the queries received by Traffic Manager and the network latency intelligence tables that the service maintains to provide you with the following:

    • The regions from where your users are connecting to your endpoints in Azure.
    • The volume of users connecting from these regions.
    • The Azure regions to which they are getting routed to.
    • Their latency experience to these Azure regions.

    This information is available for you to consume through geographical map overlay and tabular views in the portal in addition to being available as raw data for you to download.

    How can I benefit from using Traffic View?

    Traffic View gives you the overall view of the traffic your Traffic Manager profiles receive. In particular, it can be used to understand where your user base connects from and equally importantly what their average latency experience is. You can then use this information to find areas in which you need to focus, for example, by expanding your Azure footprint to a region that can serve those users with lower latency. Another insight you can derive from using Traffic View is to see the patterns of traffic to different regions which in turn can help you make decisions on increasing or decreasing invent in those regions.

    How is Traffic View different from the Traffic Manager metrics available through Azure monitor?

    Azure Monitor can be used to understand at an aggregate level the traffic received by your profile and its endpoints. It also enables you to track the health status of the endpoints by exposing the health check results. When you need to go beyond these and understand your end user's experience connecting to Azure at a regional level, Traffic View can be used to achieve that.

    Does Traffic View use EDNS Client Subnet information?

    The DNS queries served by Azure Traffic Manager do consider ECS information to increase the accuracy of the routing. But when creating the data set that shows where the users are connecting from, Traffic View is using only the IP address of the DNS resolver.

    How many days of data does Traffic View use?

    Traffic View creates its output by processing the data from the seven days preceding the day before when it is viewed by you. This is a moving window and the latest data will be used each time you visit.

    How does Traffic View handle external endpoints?

    When you use external endpoints hosted outside Azure regions in a Traffic Manager profile you can choose to have it mapped to an Azure region which is a proxy for its latency characteristics (this is in fact needed if you use performance routing method). If it has this Azure region mapping, that Azure region's latency metrics will be used when creating the Traffic View output. If no Azure region is specified, the latency information will be empty in the data for those external endpoints.

    Do I need to enable Traffic View for each profile in my subscription?

    During the preview period, Traffic View was enabled at a subscription level. As part of the improvements we made before the general availability, you can now enable Traffic View at a profile level, allowing you to have more granular enabling of this feature. By default, Traffic View will be disabled for a profile.

    If you enabled Traffic View at a subscription level during the preview time, you now need to re-enable it for each of the profile under that subscription.

    How can I turn off Traffic View?

    You can turn off Traffic View for any profile using the Portal or REST API.

    How does Traffic View billing work?

    Traffic View pricing is based on the number of data points used to create the output. Currently, the only data type supported is the queries your profile receives. In addition, you are only billed for the processing that was done when you have Traffic View enabled. This means that, if you enable Traffic View for some time period in a month and turn it off during other times, only the data points processed while you had the feature enabled count towards your bill.


    Detail

    I ran into the nasty situation where an ALTER TABLE statement failed due to a foreign key not being dropped earlier. This led to some inconsistencies in the InnoDB data dictionary (probably due to http://bugs.mysql.com/bug.php?id=58215).

    Error on rename of './db/#sql-482c_8448f' to './db/visits' (errno: 150)

    As I couldn't recover the #sql-482c_8448f table to visits, I decided to reimport it from a backup done just before the alter. However this failed. On investigation:

    • The constraint had been removed from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.STATISTICS
    • But the constraint was still visible in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
    • The table didn't exist so I couldn't drop the foreign key
    • I couldn't create the table without errors

    3 Answers 3

    PostgreSQL and many other RDBMSs often struggle with OR predicates.

    What often happens, and has happened in this case, is that the compiler decides that it has no way of implementing the two OR conditions via a single seek, and instead scans the whole index, evaluating the two (or more) conditions on every row.

    This is despite the more obvious (to a human) method of an Index Union.

    What you are doing is a very common trick to help the compiler and force an Index Union. It is now evaluating the two sides entirely separately, and in this case it is much faster.

    It may not always be faster, for example if location_type = 2 was a very large proportion of the table. The benefit is more obvious when the two conditions are very different in performance.

    For example, WHERE OR someName = @name the first condition is a straight seek on a single row, whereas the second condition is a seek to a few rows. The compiler cannot satisfy this with a single seek, it therefore often jumps to scanning the whole table. An Index Union helps here because you can utilize an index on id and another index on someName

    Jeff already hinted at this, but I feel the need to point out the elephant in the room:
    The two queries are not equivalent!

    UNION removes all duplicates across the SELECT list.
    While the other query with OR keeps them.

    You have SELECT * FROM geolocations , an no other tables in the FROM list. So if there are no duplicate rows in the table (which is guaranteed by any UNIQUE index on NOT NULL columns including PRIMARY KEY and UNIQUE constraints), there cannot be duplicate in the result and the two queries are equivalent after all. But any JOIN or any SELECT list with a (not-unique) subset of columns can introduce duplicate rows in the result!

    Using UNION ALL instead is even further off. It produces duplicates that OR -ed predicates will not. If the same row qualifies for multiple OR -ed predicates, it qualifies once. Rewriting with UNION ALL will select that same row multiple times.

    There is no way to filter "bad" duplicates and keep the "good" ones with UNION / UNION ALL . So Postgres cannot generally replace cases of "ugly OR " with UNION plans. Even where it could, it's not certain that UNION will, in fact, be faster.

    But Postgres can typically combine multiple OR -ed predicates on the same table in a bitmap index scan. See:

    An "ugly OR " is where predicates on different underlying tables are OR -ed together. (Or even the same table, but separate instances like in your case.) This can make queries more expensive, even when no indexes are involved. But it gets particularly expensive, when an efficient index scan is foiled by this. (Indexes on different tables cannot be combined in a bitmap index scan!) It typically matters most for selective queries returning a very small percentage of underlying big tables. When more than a few percent of all rows have to be read anyway, index scans lose their power. Ugly OR s don't hurt as much in those queries.

    Related blog post by Laurenz Albe:

    Solutions

    First of all, your use of CONCAT() is incorrect. It concatenates offspring with a leading / for root nodes with ancestry IS NULL . Like '/1' instead of '1' . CONCAT_WS() would be correct. Like:

    Still ugly. If you run queries like this a lot you might do more. If the table is read-only, add a boolean flag named has_children . Else consider a MATERIALIZED VIEW with that extra column or keep the table column current with triggers. Then your query can just be:

    has_children is typically not selective, so the query produces a lot of result rows and is never going to be very cheap (though a lot cheaper). Indexing the column won't help. We'd need complete information to maybe find a different angle. That's beyond the scope of this question.

    Either way, if you need that redundant ancestry with every row, consider a proper array with a GIN index on it instead of the ugly string. Or maybe the additional module ltree, which is old, but for that purpose exactly.


    BigQuery's views are logical views, not materialized views. Because views are not materialized, the query that defines the view is run each time the view is queried. Queries are billed according to the total amount of data in all table fields referenced directly or indirectly by the top-level query. For more information, see query pricing.

    • For information on creating views, see Creating views.
    • For information on creating an authorized view, see Creating authorized views.
    • For information on listing views, see Listing views.
    • For information on getting view metadata, see Getting information about views.
    • For information on updating views, see Updating views.
    • For more information on managing views, see Managing views.

    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.