Posts with tag: SQL

17 Oct 2010

Elgg Core Proposal: New Table “entity_list_items”

[This proposal has been superseded with ElggCollection.] As of Elgg 1.7.4 there’s no way to specify a specific list or ordering of entities for use in an elgg_get_entities query. E.g. one might want to implement: A “featured items” list for a group or user widget. On a group page this could be implemented in “left”

[...read the post]
In Programming
16 Feb 2010

SQL Server Mgmt Studio Can’t Export CSV

I’m trying to export large (e.g. 16M rows) SQL Server 2008 views for eventual import into MySQL. The underlying setup for these views is bizarre: A SELECT for a single row can take over two minutes, a SELECT * FROM view with no WHERE clause is much faster, but still too slow to finish an

[...read the post]
In
16 Jan 2008

Distance and bearings between GPS coordinates in SQL

Another post from my sql abominations series. I’m running this on MySQL 5 particularly. Assume you have a table of locations with Latitude and Longitude for each one. In my case the table is “station”, primary key being “LocID”. With help from this article, first we create a view to get 3D coordinates (6378 =

[...read the post]
In
15 Aug 2007

Average direction in SQL

Given a column of polar directions in degrees, this is a single SQL expression to compute their average, for use in a GROUP BY query. Some functions may be MySQL-specific. IF(DEGREES(ATAN2( -AVG(SIN(RADIANS(direction_deg))) ,-AVG(COS(RADIANS(direction_deg)))) ) < 180 ,DEGREES(ATAN2( -AVG(SIN(RADIANS(direction_deg))) ,-AVG(COS(RADIANS(direction_deg)))) ) + 180 ,DEGREES(ATAN2( -AVG(SIN(RADIANS(direction_deg))) ,-AVG(COS(RADIANS(direction_deg)))) ) – 180 ) Thought someone might like to run

[...read the post]
In