Tag Info

Hot answers tagged

16

You may be able to achieve better performance by searching first in rows with higher frequencies. This can be achieved by 'granulating' the frequencies and then stepping through them procedurally, for example as follows: --testbed and lexikon dummy data: begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set ...


13

Setup I am building on @Jack's setup, firstly because that saves time (kudos to Jack) and secondly to make it easier for people to follow and compare. Tested with PostgreSQL 9.1.4. CREATE SCHEMA x; SET search_path = x; CREATE TABLE lexikon ( id serial ,word text ,frequency int ,lset int ); INSERT INTO lexikon(word, frequency, ...


9

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available. By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by ...


7

R-tree structure works in a way that two nearby points are "closer" in the R-tree index - because both coordinates and both with same weight are used to decide where (in the index) a new point is to be placed. So, it's easy to identify points that are "near" a fixed point - meaning points that have both coordinates near the fixed point coordinates. ...


7

If I understand the question correctly (and I'm not sure I do), you are worried about computing "(Some formula to compute distance here)" for every row in the table each time you do a query? This can be mitigated to a degree by using the indexes on latitude and longitude so we only have to compute the distance for a 'box' of points containing the circle we ...


6

MongoDB has built in support for geoindexing. You don't need to do the calculation yourself. Basically, you would create a field with the lat/long stored as an array or as sub documents, something like one of these: { loc : [ 50 , 30 ] } //SUGGESTED OPTION { loc : { x : 50 , y : 30 } } { loc : { lon : 40.739037, lat: 73.992964 } } Then index the new ...


5

(Disclosure: I'm a Microsoft SQL Server guy, so my answers are influenced by that.) To really do it efficiently, there's two things you want: caching and native spatial data support. Spatial data support lets you store geography and geometry data directly in the database without doing intensive/expensive calculations on the fly, and lets you build indexes ...


5

Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time. You can achieve the same by doing the update in a single statement: UPDATE list_of_location SET location = ...


5

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WBK) format from the standards, and the MySQL internal GEOMETRY data type. Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this: INSERT INTO t1 (pt_col) ...


4

You start a transaction but don't commit the 2nd one, so the table will remain locked. The SQL Server restart will rollback the transaction containing the CREATE INDEX Remove both BEGIN TRANSACTION calls and theCOMMIT(or add a final COMMIT TRAN)


4

The FULLTEXT index acts very funny with regard to the MySQL Query Optimizer. I have written about this before: http://stackoverflow.com/a/6092216/491757 (May 23, 2011) FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011) Mysql fulltext search my.cnf optimization (Jan 26, 2012) MySQL EXPLAIN doesn't show ...


4

GPS co-ordinates are just latitude and longitude - if you have to support SQL 2005, then store them as numbers to your required precision. To calculate the distance, you can implement the Haversine formula


4

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself. Consider this simplified form: CREATE FUNCTION store_distance(_lat double precision ...


4

Correct. Spatial indexes don't get leveraged in that situation, sadly. Spatial indexes provide a set of grids, allow the system to identify geometries (or geographies) that overlap these grids. Your best bet is to set a threshold of acceptable closeness, and try that, using something like STBuffer. STIntersects works well, and you can increase this ...


4

As you can see here: http://technet.microsoft.com/en-us/library/bb895373%28v=sql.105%29.aspx, not only is the number of methods that can use a spatial index limited, it can only be used in a WHERE or JOIN ON clause. You're trying to use the STDistance method in an ORDER BY clause, where index usage is not supported. EDIT: You might get away with creating ...


3

That question is much too vague to answer. The problem defines the solution, not the other way around. For your specific use-case, I would recommend PostgreSQL + PostGIS. I have no personal experience with PostGIS, but it's a well-supported extension to PostgreSQL.


3

You are seeing too many results for $nearSphere compared with $near because with spherical geometry operators (i.e. $nearSphere), you also need to convert the any distances used in the query (i.e. $maxDistance) to radians in order to get the right result. Here, it doesn't look like you converted $maxDistance to radians. To convert from distance to radians, ...


3

If the second (indeed less restrictive) query returns zero rows while the first returns more than zero rows, then this is a bug. First check if you can repoduce the error with only table or not. If the error stays while you remove the DISTINCT and/or the ORDER BY ... LIMIT. Then try to write the set of statements (CREATE tables, INSERT rows, and the 2 ...


3

Try this one. I have moved the conditional update into a single statement because the action you were taking was the same for both conditions. Also I have altered the way that you join to the INSERTED table so that it performs the filter pre-join: CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS IF UPDATE(Latitude) OR ...


3

While not a Graph or RDBMS based solution, let me suggest a NoSQL database. IMO all of your criteria seem like they could be met with a Cassandra/Solr implementation. We use Cassandra at work for storing large amounts of data, and we serve it to various applications with a JBoss service layer. Cassandra integrates right in with the Apache Solr search ...


3

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list. use simply vendor.location.Lat or location.Lat or define a table alias and use that. CREATE TABLE dbo.Vendor([location] geography) GO /*Works fine*/ CREATE VIEW V1 AS SELECT v.location.Lat AS latitude FROM dbo.vendor v GO /*Fails*/ CREATE VIEW V2 AS SELECT ...


3

Basic answer I suggest to use the geometric type box and combine that with an exclusion constraint (Postgres 9.2+). Should be the perfect solution to your problem. The GiST index this is implemented with (automatically) also supports certain queries. Combine it with with equality on board_id to hold multiple boards in a single table. You'll need the ...


2

Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve. A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest. So... when trying to find the closest item, start with a small distance and find items in that ...


2

A query in MongoDB can only use one index at a time, so it's a case of one or the other - it can't use the 2d index first, then do a sort on the _id index. In order to use indexes for both the selection and the sort, you would need a compound index like this: db.markers.ensureIndex( { latlng : "2d" , _id : 1 } ); Try that, or similar and see how it ...


2

Well you could use IBM DB2 LUW (Linux,Unix, Windows) Express-C edition. It is free and has community support. It is the same engine/binaries as DB2 Enterprise Edition, it just has certain features "turned off" and has memory and CPU caps, but for what you are describing, it may suit your needs. If you do find you need more memory/CPU you could always ...


2

Microsoft connect ticket was resolved with wording changed: Please refer to link below (changes have not been made on BOL yet, 1 dec) https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect


2

I think you want MBRContains I have an example from a previous post : Less restrictive query return less result due to simple removing one additional constraint Give it a Try !!! UPDATE 2013-05-08 17:05 EDT You have this: SELECT * FROM tb_gps WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)) Put the MULTIPOINT ...


2

I would use Excel 2013 - it has fairly easy Map integration which can take Lat+Lon, eg: http://www.sqljason.com/2013/03/geospatial-analytics-microsoft-bi-john.html I would pull a SQL view through PowerPivot for easy derivation of .Lat and .Long, and for refreshing the data. Alternatively SQL Server Reporting Services 2008 R2 or 2012 has similar Map ...


2

After doing some research, including looking at this question, it appears it isn't directly possible to efficiently bulk load any of the CLR-based types, including geography. 1 You said that adding just the geography column added a significant amount of time to the load process -- this may, in fact, be entirely reasonable, depending on the amount of data ...


1

Try this instead, with (or without) decimal points DECLARE @a geography SET @a=geography::STPointFromText('POINT(0.0 90.0)', 4326) if this works - then something wrong with the ITEMS table



Only top voted, non community-wiki answers of a minimum length are eligible