Tag Info

Hot answers tagged

18

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 TABLE lexikon ( lex_id serial PRIMARY KEY ,word text ,frequency int NOT NULL -- we'd need to do more if NULL was allowed ,lset int ); INSERT INTO ...


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 ...


8

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 ...


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

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 ...


7

Sounds like you want to get rowgoals to play their part on the query - so try using TOP(1), maybe with testing to avoid NULLs (in case of non-matching SRIDs). That way you can get the "nearest neighbour" functionality to kick in. I know you're using Contains, but you want to use a method that tells the QO that you're only going to get a single row back. ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


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

(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

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) 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

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

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

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

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 ...


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 ...


4

Isn't this what you're looking for ? CREATE TRIGGER [triggername] ON [table] AFTER INSERT, UPDATE AS BEGIN UPDATE [table] SET [table].Updated = Getdate() WHERE ID IN (SELECT ID FROM INSERTED) END GO


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

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

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

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

PostGIS has support for it. See http://www.postgis.org/docs/ST_GeoHash.html.


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

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item. Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run ...


3

The simplest solution without PostGIS would be to store lat/long as two number columns. numeric for exact precision. double precision or even just real if you don't need the precision. I see no reason why the data type point shouldn't work as well. Per documentation: Points are the fundamental two-dimensional building block for geometric types. Values ...


3

As Ypercube said in his comment you seem to have an uncorrelated subquery. This means that for each way you are trying to build a line containing 1,000,000 odd nodes. Also it it would be best to put a Geography constructor around the result of the subquery. The tutorial's update statement that you referenced has got a correlated subquery, because the ...


3

Look at the estimated query plan, and make sure the index is being used. Also, the complexity of your MultiPolygon could be a significant factor. If you imagine your index as a series of grids over your MultiPolygon, there will be grids that either completely covered by your MultiPolygon or completely not covered by your MultiPolygon. Your ProblemChild ...



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