StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>For doing this <em>efficiently in SQL</em> I would recommend you to choose a DBMS with spatial support (often going under the name <a href="http://en.wikipedia.org/wiki/Geographic_information_system" rel="nofollow noreferrer">GIS</a> support) or with support for two- or multi-dimensional indexes (I don't know any such).</p> <p>I think only then will you be able to make your queries use indexes to perform efficiently.</p> <p>Postgres has spatial support (under the name <a href="http://postgis.refractions.net/" rel="nofollow noreferrer">PostGIS</a>), as does MS SQL 2008 and Oracle. I only used MS SQL 2008 for spatial, <a href="http://technet.microsoft.com/en-us/library/bb964712.aspx" rel="nofollow noreferrer">here is a nice overview of how its spatial index subdivides the surface</a>.</p> <p>Without multi-dimensional indexes you'll have your objects on disk ordered along one coordinate axis, consequently you will only be able to narrow down the table scan for objects only along one coordinate axis.</p> <hr> <p>If you want to stay with MySQL (reply to OP comment below):</p> <p>I think it is better to create the query with concrete values - whether you do that in PHP or in a stored procedure on the SQL side, is a different question. But it is simple and easy enough to do in PHP so I would recommend that.</p> <p>You can make decisions like whether the viewport is in a non-wraparound position (blue in your diagram above) and then its enough to emit a single query with a simple <code>WHERE xmin>=viewport_xmin AND xmax<=viewport_xmax AND ymin>=viewport_ymin AND ymax<=viewport_ymax</code> clause. Or see that the viewport wraps, and then experiment whether it is faster to execute 2/4 separate simple queries for each of the 2/4 unwrapping parts of the portal, or put all the 2/4 simple queries with <code>OR</code>s between them in one big query. You will have to see which one will be faster, experiment with indexes, look at query plans, etc.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload