Hot answers tagged spatial
11
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 ...
9
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, ...
8
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 ...
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 = ...
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 ...
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
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
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.
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
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
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
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) ...
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 ...
1
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 ...
1
I figured it out. I used a query for the SQL Server source that converts to POINT text using:
geometry::STGeomFromWKB(myPointColumn.STAsBinary(), myPointColumn.STSrid).STAsText()
That was all it took. Now whether the point will be represented in PostGIS the way I intend it to I can't say at this point, but I'm hoping so.
1
You could use ogr2ogr.
I'm not certain if you can do a direct import, but at the very least you can export to a SHP file, and then import the SHP file to PostGIS with shp2pgsql. It ships with PostGIS. b
1
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 ...
1
Below is a simplistic, single-query way to do it as a list of small 2 point line segments in one query, without using cursors. There's a lot more that can be done if you build and store your lines in another table with a cursor and the .STUnion method and your point spatialdata field.
Assumptions and changes: 1. includes city and state. 2. to be more ...
1
It turned out to be that I needed to convert to the geography type differently. Building off of @Mr.Brownstone's answer (which was helpful since it simplified the trigger):
CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
IF UPDATE(Latitude) OR UPDATE(Longitude)
UPDATE pt
SET pt.GeocodedAddress = ...
1
Using MyISAM is probably your best and only bet to be able to use a spatial index. You might consider storing the data in an InnoDB table and then export data continuously or by batch, according to your needs, to a myisam table. Then use the myisam table for your queries.
I wouldn't trust myisam as storage engine if data is of importance.
Seems like you ...
1
The classic answer is to query the database for the set of points within the 100 mile BOX and then remove the subset of points that are outside the 100 mile CIRCLE in your application layer using your programming language of choice (not SQL).
I've done this with data in Amazon SimpleDB efficiently, and you could do the same in DynamoDB with a couple "index ...
1
i suggest 2 options
Option 1: if you want a RBDMs, i think 2 tables will be sufficient
Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)
in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and ...
1
You could try GRASS or QGIS., open-source GIS applications. You might be able to get what you want from one of them.
EDIT: I see you're after a data modelling tool with support for spatial data types. I can't see any specific reference with basic google-fu. You may be stuck with building your database model using CREATE TABLE statements and then reverse ...
1
I totally agree with all the statements of the first answer, but sharing my own experience -I’ve made this on my country's National Roads Administration: production critic, high traffic site. I suggest a web app be feed by both MySQL and PostgreSQL/PostGIS.
For all the "typical" stuff, the web app works flawlessly with a MySQL based CMS. For all the ...
Only top voted, non community-wiki answers of a minimum length are eligible
