| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Comments:
<0> how can i change it?
<1> well
<1> you read the documentation on doing that
<0> do u know how php + oracle work
<0> do u hav any docu
<1> yes
<1> but you're asking about mysql
<0> can u help me
<1> nope
<1> but the documentation can
<1> @oracle
<2> Listing of all oracle functions:
<2> ora_bind, ora_close, ora_columnname, ora_columnsize, ora_columntype, ora_commit, ora_commitoff, ora_commiton, ora_do, ora_error, ora_errorcode, ora_exec, ora_fetch_into, ora_fetch, ora_getcolumn, ora_logoff, ora_logon, ora_numcols, ora_numrows, ora_open, ora_parse, ora_plogon, ora_rollback
<2> http://www.php.net/oracle
<3> Zenethian: i ended up with this
<3> select w.bssid, (point(avg(g.loc[0]), avg(g.loc[1]))) AS location FROM wireless_networks w JOIN gps_info g USING (bssid) GROUP BY bssid HAVING point(avg(g.loc[0]),avg(g.loc[1])) @ box '((29.682087444299334,-95.25764465332031),(29.41208667100814,94.77561950683594))';
<1> follower: that looks about right
<3> but
<3> i want all of my columns from the first table
<1> then add all of them in
<3> i dont know how
<1> w.*,
<3> uhhh
<3> what is with this whole w. thing?
<1> its an alias?
<1> FROM wireless_networks w
<1> FROM wireless_networks [AS] w
<3> hmmm
<1> it defines it as shorthand for your table name
<3> interesting, i never read that anywhere
<1> if you add all of the original columns, you have to add them all to your group by statement as well
<1> something that might be easier to do is use that query as a subquery
<1> select w.* from wireless_networks as w where w.bssid in (<query here>)
<1> and even better yet, do them as two separate views
<1> for consistancy
<3> select w.*, (point(avg(g.loc[0]), avg(g.loc[1]))) AS location FROM wireless_networks w JOIN gps_info g USING (bssid) GROUP BY bssid, ssid, type, wep, encryption, channel, cloaked, ip_type, ip_range, carrier, first_seen, last_seen HAVING point(avg(g.loc[0]),avg(g.loc[1])) @ box '((29.682087444299334,-95.25764465332031),(29.41208667100814,94.77561950683594))';
<3> that makes for a freakin 26 second query
<1> yeah
<3> jesus
<1> try with the subselect instead
<1> grouping like that is not recommended. :P
<3> for obvious reasons i guess
<1> ****, you can't subselect
<4> oh yeah
<1> it's two return values
<4> high tech hitler
<3> hmm, so how do i make that query faster?
<3> because 26 seconds is freakin outrageous
<1> don't return all the columns
<1> make that a view and then join it to itself?
<3> if i do just the bssid its a 5.6s query
<3> i **** at sql, but im learning
<1> yeah
<1> how fast is the machine you're doing this on?
<3> ummm
<3> p4 2.8
<1> beaause that's one ****ing ugly query dude
<3> yeah, but i dont know how else to do it
<1> is there a reason you're doing this on the DB side?
<3> except for the way im doing it now
<3> yeah, because it takes 28s to do it with php
<1> lol
<1> it sounds like it's just gonna be a slow query then
<3> vs 5.6s with sql
<1> you could try and index loc
<1> but I don't think it'll help any
<1> the boxing is a tedious algorithm
<3> its very needed though
<5> HAVING is pretty intensive, isn't it?
<1> it's less intensive than you think
<3> its points being mapped on good maps, so im filtering out stuff that isnt visible
<1> HAVING is just an aggregate over already-grouped data
<1> it isn't too bad
<1> overall you're just stuck with a long running query
<6> HAVING is just a WHERE for grouped data from an aggregate function
<3> its much better than how i was having it
<3> i wonder how bad this is going to get when i completly populate the database
<3> right now its only 26,000+ aps, and 509,000 points
<1> waitr
<1> you're doing the averaging twice
<3> no
<3> its avg(loc[0]) and avg(loc[1])
<1> yeah
<1> but you do that twice in your query
<3> i dont think there is a function to avg point
<1> once in the select list, and once in the having
<3> how else can i do it though?
<3> wait
<6> I shot the hot sticky load all over evulish's mom
<1> uh, reference that result in the having?
<3> nope, i dont know
<3> i dont know how to reference
<1> aliases
<6> my **** is so thick it comes out as noodles
<3> i did an alias of location
<1> select w.bssid, (point(avg(g.loc[0]), avg(g.loc[1]))) AS location FROM wireless_networks w JOIN gps_info g USING (bssid) GROUP BY bssid HAVING location @ box '((29.682087444299334,-95.25764465332031),(29.41208667100814,94.77561950683594))';
<7> o rly
<3> i tried that, ERROR: column "location" does not exist
<1> ah, right
<1> okay
<1> How about two individual queries.
<7> agreed
<1> break away the grouping first
<6> is this mysql?
<1> pg
<3> postgres
<6> thought so
<6> gis **** eh
<1> select w.bssid, (point(avg(g.loc[0]), avg(g.loc[1]))) AS location FROM wireless_networks w JOIN gps_info g USING (bssid);
<1> as your first query
<1> make that a view
<1> then do
<1> ah **** that wont work either.
<1> I was gonna say
<1> SELECT bssid, location FROM your_view GROUP BY bssid HAVING location @ box '((29.682087444299334,-95.25764465332031),(29.41208667100814,94.77561950683594))';
<1> but that still has the same problem
<6> try ::location
<6> no
<6> nevermind
<1> dude
<1> I know
<1> write this as an optimized C function in pgsql :P
<3> bah
<1> get a faster box
<3> i know php/perl/java/js, c is not included
<1> or live with 5.6ms. :P
<1> er, 5.6s
<3> sql hurts my head, i think i need nicotine
<1> nicotine hurts your lungs, you need sql
<3> i have to take the trash out anyways
<3> s/have/need
<3> and i need to do that before athf comes on
<3> so bbias
<5> sweet. we got a mouse
<1> wtf are you talking about evulish ?
<5> we laid traps
<5> sadly, they're the horrible ones
<1> oh jesus
<6> get a pvr
<1> and you're watching them?
<1> or you're just commenting on what you've found?
<1> you should put a webcam on them
<6> make a homemade trap
<6> catch it unscathed, and drown it in urine
<5> it's one of those traps that they get stuck in
<5> doesn't kill them
<6> might as well
<6> they chew off their limbs to get out of it
<4> poutine, you like history?
<6> not to mention rip their muscles
<6> trollboy, somewhat
Return to
#php
or
Go to some related
logs:
tamil- bratha
Uyirile Kalanthathu
#india
#allnitecafe
net.ipv4.ip_forward fedora c5
kartun maxis
#linux
#allnitecafe
#linux
#allnitecafe
|
|