Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>1)</strong> At first filter your SQLite data with a good approximation and decrease amount of data that you need to evaluate in your java code. Use the following procedure for this purpose:</p> <p>To have a deterministic <strong>threshold</strong> and more accurate filter on data, It is better to calculate <strong>4 locations</strong> that are in <code>radius</code> meter of the north, west, east and south of your central point <strong>in your java code</strong> and then <strong>check easily by less than and more than SQL operators (>, &lt;)</strong> to determine if your points in database are in that rectangle or not.</p> <p>The method <code>calculateDerivedPosition(...)</code> calculates those points for you (p1, p2, p3, p4 in picture).</p> <p><img src="https://i.stack.imgur.com/6wzgv.png" alt="enter image description here"></p> <pre><code>/** * Calculates the end-point from a given source at a given range (meters) * and bearing (degrees). This methods uses simple geometry equations to * calculate the end-point. * * @param point * Point of origin * @param range * Range in meters * @param bearing * Bearing in degrees * @return End-point from the source given the desired range and bearing. */ public static PointF calculateDerivedPosition(PointF point, double range, double bearing) { double EarthRadius = 6371000; // m double latA = Math.toRadians(point.x); double lonA = Math.toRadians(point.y); double angularDistance = range / EarthRadius; double trueCourse = Math.toRadians(bearing); double lat = Math.asin( Math.sin(latA) * Math.cos(angularDistance) + Math.cos(latA) * Math.sin(angularDistance) * Math.cos(trueCourse)); double dlon = Math.atan2( Math.sin(trueCourse) * Math.sin(angularDistance) * Math.cos(latA), Math.cos(angularDistance) - Math.sin(latA) * Math.sin(lat)); double lon = ((lonA + dlon + Math.PI) % (Math.PI * 2)) - Math.PI; lat = Math.toDegrees(lat); lon = Math.toDegrees(lon); PointF newPoint = new PointF((float) lat, (float) lon); return newPoint; } </code></pre> <p>And now create your query:</p> <pre><code>PointF center = new PointF(x, y); final double mult = 1; // mult = 1.1; is more reliable PointF p1 = calculateDerivedPosition(center, mult * radius, 0); PointF p2 = calculateDerivedPosition(center, mult * radius, 90); PointF p3 = calculateDerivedPosition(center, mult * radius, 180); PointF p4 = calculateDerivedPosition(center, mult * radius, 270); strWhere = " WHERE " + COL_X + " &gt; " + String.valueOf(p3.x) + " AND " + COL_X + " &lt; " + String.valueOf(p1.x) + " AND " + COL_Y + " &lt; " + String.valueOf(p2.y) + " AND " + COL_Y + " &gt; " + String.valueOf(p4.y); </code></pre> <p><code>COL_X</code> is the name of the column in the database that stores latitude values and <code>COL_Y</code> is for longitude.</p> <p>So you have some data that are near your central point with a good approximation.</p> <p><strong>2)</strong> Now you can loop on these filtered data and determine if they are really near your point (in the circle) or not using the following methods:</p> <pre><code>public static boolean pointIsInCircle(PointF pointForCheck, PointF center, double radius) { if (getDistanceBetweenTwoPoints(pointForCheck, center) &lt;= radius) return true; else return false; } public static double getDistanceBetweenTwoPoints(PointF p1, PointF p2) { double R = 6371000; // m double dLat = Math.toRadians(p2.x - p1.x); double dLon = Math.toRadians(p2.y - p1.y); double lat1 = Math.toRadians(p1.x); double lat2 = Math.toRadians(p2.x); double a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLon / 2) * Math.sin(dLon / 2) * Math.cos(lat1) * Math.cos(lat2); double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a)); double d = R * c; return d; } </code></pre> <p>Enjoy!</p> <p>I used and customized <a href="https://stackoverflow.com/a/1125425/779408">this reference</a> and completed it.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQlite Getting nearest locations (with latitude and longitude)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBeeOnRope
    1. USbreceivemail
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQlite Getting nearest locations (with latitude and longitude)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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