Mixing
How to query based off of a relationship?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I have two tables:
User
id
and
Region
id, userID, origin (Geometry('point')), radius (Double)
User has a one-to-one relationship with Region.
I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.
I'm able to query just regions that contain a given point like this:
SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:
SELECT *
FROM user, regions as region
WHERE
user.id = region.userID
AND
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;
With this query, I don't get any results, where the first query I get results.
postgresql spatial postgis
edited 44 mins ago
Evan Carroll
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
I have two tables:
User
id
and
Region
id, userID, origin (Geometry('point')), radius (Double)
User has a one-to-one relationship with Region.
I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.
I'm able to query just regions that contain a given point like this:
SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:
SELECT *
FROM user, regions as region
WHERE
user.id = region.userID
AND
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;
With this query, I don't get any results, where the first query I get results.
postgresql spatial postgis
edited 44 mins ago
Evan Carroll
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
Okay, so are you suggesting I use my original query, but add some type ofJOIN?
– KKendall
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have two tables:
User
id
and
Region
id, userID, origin (Geometry('point')), radius (Double)
User has a one-to-one relationship with Region.
I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.
I'm able to query just regions that contain a given point like this:
SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:
SELECT *
FROM user, regions as region
WHERE
user.id = region.userID
AND
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;
With this query, I don't get any results, where the first query I get results.
postgresql spatial postgis
edited 44 mins ago
Evan Carroll
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have two tables:
User
id
and
Region
id, userID, origin (Geometry('point')), radius (Double)
User has a one-to-one relationship with Region.
I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.
I'm able to query just regions that contain a given point like this:
SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:
SELECT *
FROM user, regions as region
WHERE
user.id = region.userID
AND
ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;
With this query, I don't get any results, where the first query I get results.
postgresql spatial postgis
postgresql spatial postgis
edited 44 mins ago
Evan Carroll
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 44 mins ago
Evan Carroll
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 44 mins ago
Evan Carroll
29.6k860191
edited 44 mins ago
Evan Carroll
29.6k860191
edited 44 mins ago
Evan Carroll
29.6k860191
29.6k860191
asked 1 hour ago
KKendall
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
KKendall
1085
asked 1 hour ago
KKendall
1085
1085
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
KKendall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
Okay, so are you suggesting I use my original query, but add some type ofJOIN?
– KKendall
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago
add a comment |Â
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
Okay, so are you suggesting I use my original query, but add some type ofJOIN?
– KKendall
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
Okay, so are you suggesting I use my original query, but add some type of
JOIN?– KKendall
1 hour ago
Okay, so are you suggesting I use my original query, but add some type of
JOIN?– KKendall
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
ST_MakePoint(lat, long)
That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint
AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
That doesn't make any sense, it should be
ST_DWithin(origin, ST_MakePoint(long,lat), radius)
I think this is what you want,
SELECT *
FROM user AS u
INNER JOIN regions AS r
ON u.id = r.userID
AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY
maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?
Don't forget to add your spatial index. In this case you can actually add a compound index.
CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);
See also,
- Finding the nearest geo points across two tables?
answered 45 mins ago
Evan Carroll
29.6k860191
Thank you for the post. I'll try it out.maxDistanceandradiusare two different values.maxDistanceis a parameter to limit the distance frompointto theorigins ofregion.radiusrepresents a circle around the origin of each origin.ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0is actually what I want.
– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
If you have a radius larger thanmaxDistancethen the values aren't the same. You would need only use the more restrictive (smaller) one.
– Evan Carroll
19 mins ago
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
 |Â
show 3 more comments
up vote
1
down vote
I have just modified your first query and add the join condition.
SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
u."id"
FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
WHERE
ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
AND
ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
I think there's some sort of alias missing withu, but I'm not sure where it should go. The last value of theSELECTisu."id". But I can't find whereuis defined.
– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
Thank you for the answer! I'm trying to use it. I changedFROM "regions" AS "region" as rtoFROM "regions" AS r. But I'm still gettingschema "r" does not exist.
– KKendall
55 mins ago
Oh, I found it. I need to remover.fromr.ST_Distance(...
– KKendall
54 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
ST_MakePoint(lat, long)
That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint
AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
That doesn't make any sense, it should be
ST_DWithin(origin, ST_MakePoint(long,lat), radius)
I think this is what you want,
SELECT *
FROM user AS u
INNER JOIN regions AS r
ON u.id = r.userID
AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY
maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?
Don't forget to add your spatial index. In this case you can actually add a compound index.
CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);
See also,
- Finding the nearest geo points across two tables?
answered 45 mins ago
Evan Carroll
29.6k860191
Thank you for the post. I'll try it out.maxDistanceandradiusare two different values.maxDistanceis a parameter to limit the distance frompointto theorigins ofregion.radiusrepresents a circle around the origin of each origin.ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0is actually what I want.
– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
If you have a radius larger thanmaxDistancethen the values aren't the same. You would need only use the more restrictive (smaller) one.
– Evan Carroll
19 mins ago
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
 |Â
show 3 more comments
up vote
1
down vote
accepted
ST_MakePoint(lat, long)
That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint
AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
That doesn't make any sense, it should be
ST_DWithin(origin, ST_MakePoint(long,lat), radius)
I think this is what you want,
SELECT *
FROM user AS u
INNER JOIN regions AS r
ON u.id = r.userID
AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY
maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?
Don't forget to add your spatial index. In this case you can actually add a compound index.
CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);
See also,
- Finding the nearest geo points across two tables?
answered 45 mins ago
Evan Carroll
29.6k860191
Thank you for the post. I'll try it out.maxDistanceandradiusare two different values.maxDistanceis a parameter to limit the distance frompointto theorigins ofregion.radiusrepresents a circle around the origin of each origin.ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0is actually what I want.
– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
If you have a radius larger thanmaxDistancethen the values aren't the same. You would need only use the more restrictive (smaller) one.
– Evan Carroll
19 mins ago
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
 |Â
show 3 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
ST_MakePoint(lat, long)
That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint
AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
That doesn't make any sense, it should be
ST_DWithin(origin, ST_MakePoint(long,lat), radius)
I think this is what you want,
SELECT *
FROM user AS u
INNER JOIN regions AS r
ON u.id = r.userID
AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY
maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?
Don't forget to add your spatial index. In this case you can actually add a compound index.
CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);
See also,
- Finding the nearest geo points across two tables?
answered 45 mins ago
Evan Carroll
29.6k860191
ST_MakePoint(lat, long)
That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint
AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
That doesn't make any sense, it should be
ST_DWithin(origin, ST_MakePoint(long,lat), radius)
I think this is what you want,
SELECT *
FROM user AS u
INNER JOIN regions AS r
ON u.id = r.userID
AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY
maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?
Don't forget to add your spatial index. In this case you can actually add a compound index.
CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);
See also,
- Finding the nearest geo points across two tables?
answered 45 mins ago
Evan Carroll
29.6k860191
edited 20 mins ago
answered 45 mins ago
Evan Carroll
29.6k860191
answered 45 mins ago
Evan Carroll
29.6k860191
answered 45 mins ago
Evan Carroll
29.6k860191
29.6k860191
Thank you for the post. I'll try it out.maxDistanceandradiusare two different values.maxDistanceis a parameter to limit the distance frompointto theorigins ofregion.radiusrepresents a circle around the origin of each origin.ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0is actually what I want.
– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
If you have a radius larger thanmaxDistancethen the values aren't the same. You would need only use the more restrictive (smaller) one.
– Evan Carroll
19 mins ago
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
 |Â
show 3 more comments
Thank you for the post. I'll try it out.maxDistanceandradiusare two different values.maxDistanceis a parameter to limit the distance frompointto theorigins ofregion.radiusrepresents a circle around the origin of each origin.ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0is actually what I want.
– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
If you have a radius larger thanmaxDistancethen the values aren't the same. You would need only use the more restrictive (smaller) one.
– Evan Carroll
19 mins ago
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
Thank you for the post. I'll try it out.
maxDistance and radius are two different values. maxDistance is a parameter to limit the distance from point to the origins of region. radius represents a circle around the origin of each origin. ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0 is actually what I want.– KKendall
39 mins ago
Thank you for the post. I'll try it out.
maxDistance and radius are two different values. maxDistance is a parameter to limit the distance from point to the origins of region. radius represents a circle around the origin of each origin. ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0 is actually what I want.– KKendall
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
Those are the same thing, if you don't get that think about it. If I have a PointA and PointB, and PointA is 1 m away from PointB; then PointA is within maxdistance = 1 m. But the radius of intersection between PointA and the PointB is also 1 m. There is no difference at all.
– Evan Carroll
39 mins ago
1
1
If you have a radius larger than
maxDistance then the values aren't the same. You would need only use the more restrictive (smaller) one.– Evan Carroll
19 mins ago
If you have a radius larger than
maxDistance then the values aren't the same. You would need only use the more restrictive (smaller) one.– Evan Carroll
19 mins ago
1
1
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
Nice, that's perfect. Thanks for the help!
– KKendall
18 mins ago
1
1
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
I will, I'm still trying it out with some small changes to my exact scenario.
– KKendall
15 mins ago
 |Â
show 3 more comments
up vote
1
down vote
I have just modified your first query and add the join condition.
SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
u."id"
FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
WHERE
ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
AND
ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
I think there's some sort of alias missing withu, but I'm not sure where it should go. The last value of theSELECTisu."id". But I can't find whereuis defined.
– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
Thank you for the answer! I'm trying to use it. I changedFROM "regions" AS "region" as rtoFROM "regions" AS r. But I'm still gettingschema "r" does not exist.
– KKendall
55 mins ago
Oh, I found it. I need to remover.fromr.ST_Distance(...
– KKendall
54 mins ago
add a comment |Â
up vote
1
down vote
I have just modified your first query and add the join condition.
SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
u."id"
FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
WHERE
ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
AND
ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
I think there's some sort of alias missing withu, but I'm not sure where it should go. The last value of theSELECTisu."id". But I can't find whereuis defined.
– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
Thank you for the answer! I'm trying to use it. I changedFROM "regions" AS "region" as rtoFROM "regions" AS r. But I'm still gettingschema "r" does not exist.
– KKendall
55 mins ago
Oh, I found it. I need to remover.fromr.ST_Distance(...
– KKendall
54 mins ago
add a comment |Â
up vote
1
down vote
up vote
1
down vote
I have just modified your first query and add the join condition.
SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
u."id"
FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
WHERE
ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
AND
ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
I have just modified your first query and add the join condition.
SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
u."id"
FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
WHERE
ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
AND
ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
ORDER BY distance ASC
LIMIT 10;
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
edited 1 hour ago
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
answered 1 hour ago
Md Haidar Ali Khan
3,39052240
3,39052240
I think there's some sort of alias missing withu, but I'm not sure where it should go. The last value of theSELECTisu."id". But I can't find whereuis defined.
– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
Thank you for the answer! I'm trying to use it. I changedFROM "regions" AS "region" as rtoFROM "regions" AS r. But I'm still gettingschema "r" does not exist.
– KKendall
55 mins ago
Oh, I found it. I need to remover.fromr.ST_Distance(...
– KKendall
54 mins ago
add a comment |Â
I think there's some sort of alias missing withu, but I'm not sure where it should go. The last value of theSELECTisu."id". But I can't find whereuis defined.
– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
Thank you for the answer! I'm trying to use it. I changedFROM "regions" AS "region" as rtoFROM "regions" AS r. But I'm still gettingschema "r" does not exist.
– KKendall
55 mins ago
Oh, I found it. I need to remover.fromr.ST_Distance(...
– KKendall
54 mins ago
I think there's some sort of alias missing with
u, but I'm not sure where it should go. The last value of the SELECT is u."id". But I can't find where u is defined.– KKendall
1 hour ago
I think there's some sort of alias missing with
u, but I'm not sure where it should go. The last value of the SELECT is u."id". But I can't find where u is defined.– KKendall
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
@KKendall, I have modified the query , hope now it seems to be OK. Because I forgot to create the alias of user as "u" in the query.
– Md Haidar Ali Khan
1 hour ago
1
1
Thank you for the answer! I'm trying to use it. I changed
FROM "regions" AS "region" as r to FROM "regions" AS r. But I'm still getting schema "r" does not exist.– KKendall
55 mins ago
Thank you for the answer! I'm trying to use it. I changed
FROM "regions" AS "region" as r to FROM "regions" AS r. But I'm still getting schema "r" does not exist.– KKendall
55 mins ago
Oh, I found it. I need to remove
r. from r.ST_Distance(...– KKendall
54 mins ago
Oh, I found it. I need to remove
r. from r.ST_Distance(...– KKendall
54 mins ago
add a comment |Â
KKendall is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
KKendall is a new contributor. Be nice, and check out our Code of Conduct.
KKendall is a new contributor. Be nice, and check out our Code of Conduct.
KKendall is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221968%2fhow-to-query-based-off-of-a-relationship%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join.
– Md Haidar Ali Khan
1 hour ago
Okay, so are you suggesting I use my original query, but add some type of
JOIN?– KKendall
1 hour ago
I have add the alias in your first query and the join condition.
– Md Haidar Ali Khan
1 hour ago