|
I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it.
|
# ? Dec 17, 2010 01:33 |
|
|
# ? Jun 8, 2024 08:15 |
|
gotly posted:I am looking for a program like Microsoft Visio that can be used to visually mock-up data models. Something web based would be awesome. I haven't tried it much (it looks really complex) but last time someone asked this yEd was suggested.
|
# ? Dec 17, 2010 01:42 |
|
Aturaten posted:So my SQL is rusty as all gently caress, so do you think anyone could link me to a sample database that I could play around with and get back up to speed? Heres a few good ones: http://dev.mysql.com/doc/index-other.html
|
# ? Dec 17, 2010 07:40 |
|
Gilg posted:I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it. code:
Here's the function: code:
|
# ? Dec 17, 2010 16:04 |
|
Gilg posted:I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it. I made this. It's probably terrible, but it works. I also made a thing that allows the user to poll Google Maps for driving distance/time. code:
So here, I guess. The 3956.6 gives you miles. code:
butt dickus fucked around with this message at 16:10 on Dec 17, 2010 |
# ? Dec 17, 2010 16:07 |
|
Thanks slartibartfast and Doctor rear end in a top hat, your posts were helpful.
|
# ? Dec 17, 2010 23:47 |
|
Gilg posted:Thanks slartibartfast and Doctor rear end in a top hat, your posts were helpful. I guess the degree of helpfulness depends on what database you're using.
|
# ? Dec 18, 2010 00:16 |
|
Doctor rear end in a top hat posted:I guess the degree of helpfulness depends on what database you're using. If he's using Oracle I wrote a PL/SQL stored procedure to do the same thing and could dig it up. I ended up doing the same as you though and making something to poll Google because as the crow flys isn't really useful unless you're calculating distances for your scud missile launcher.
|
# ? Dec 18, 2010 06:37 |
|
Gilg posted:I think this is just a syntax question, but I haven't been able to find the answer with Google. I've got a table with an ID, latitude, and longitude and I'm looking to get the nearest X neighbors for each row. I already have a function that will measure the distance between latitude and longitude pairs, but I can't wrap my head around the best query to get the nearest neighbors. I'm tentatively falling back to a cursor-based solution, and this query is rarely run so that wouldn't be too terrible, but if there's a better way to do it, I'd love to know it. code:
Primpod fucked around with this message at 18:53 on Dec 18, 2010 |
# ? Dec 18, 2010 18:46 |
|
I am looking for some advice on creating a database out of some XML data. I expect to have to coddle it a little bit, but I also expect it to be easier and more robust than having to create a table myself. I need to take a huge pile of XML files and turn them into a tabular report. Each file has a little bit of the data for this table. Each row in the table would be determined by a name and a number. Each XML file will have that information. Any extra information then I would need to insert on the fly, potentially creating new columns as I go. I am aware of the nature of this data and know the best way to represent that data when I need to create new columns. I don't expect to be able to just give the XML files raw and hope for the best. Still, I think it will be a lot cleaner to have a database keeping everything in a table rather than having, say, many parallel hash maps or lists involved.
|
# ? Dec 20, 2010 23:34 |
|
Most DBMSs (e.g. Oracle, MySQL) have varying degrees of support for importing XML and stuffing it into tables without having to write your own parser. Which are you looking at?
|
# ? Dec 21, 2010 00:50 |
|
Namlemez posted:Most DBMSs (e.g. Oracle, MySQL) have varying degrees of support for importing XML and stuffing it into tables without having to write your own parser. Which are you looking at?
|
# ? Dec 21, 2010 04:12 |
General database question: Each object in my database is described by it's position in 64-dimensional space. This translates to each object having 64 floating point numbers between one and zero. If I wanted to do nearest neighbor search inside of a database, is using MySQL (or any database of the sort) recommended? Where can I learn more about this sort of transaction? EDIT: Think I found a solution. Still open to suggestions, but will shuffle through, "K Nearest Neighbor Queries and KNN-Joins in Large Relational Databases (Almost) for Free" by Yao, Li, and Kuman. Jo fucked around with this message at 20:36 on Dec 21, 2010 |
|
# ? Dec 21, 2010 06:28 |
|
Rocko Bonaparte posted:MySQL, or even sqlite. I want to use the database as an intermediary for producing the table I want, after which I generate a report from a big query on it and purge it. If the xml has any real level of complexity you should give some serious thought to writing the ingest script in PHP. You can do a lot in MySQL, but part of learning how to use it is learning when to use other, more efficient tools, like scripting languages.
|
# ? Dec 21, 2010 07:38 |
|
TildeATH posted:If the xml has any real level of complexity you should give some serious thought to writing the ingest script in PHP. You can do a lot in MySQL, but part of learning how to use it is learning when to use other, more efficient tools, like scripting languages.
|
# ? Dec 21, 2010 17:47 |
|
Been trying to write some SQL to take caption codes piled into a string (from a multiselect, e.g. ',Ch,Ca,'), split out the codes, go looking for the language captions and pile them all back into a string (i.e. 'Calm, Cheerful' or 'Alegre, Tranquilo'*). Got it all working, aside from piling the finished string back together. * This isn't an actual Spanish translation, we actually only support English at the moment . However, the application is built with support for internationalisation blah blah blah. Anyway, issue as follows: code:
Also, is there any way to avoid this behaviour (do I gently caress around with ANSI padding or something)? I'm working around it by using DATALENGTH, but no prizes for spotting the problem there. DATALENGTH measures the number of bytes. Unicode characters - welp. Thel fucked around with this message at 01:22 on Dec 30, 2010 |
# ? Dec 30, 2010 01:15 |
|
(double posting because it's two separate problems.) If I ever meet the person who put this advertisement-for-cutting-losses-and-starting-again of a system together, there will be blood, tears and a fair amount of descriptive verbiage. We have Clients, Clients have goals (multiple). Clients also have a dream (singular) - something they want to do, somewhere they want to go, whatever. No prizes for guessing where the dream is stored. There's a dreams field in each goal. No prizes for guessing whether dreams are kept up to date. We have clients with five different dreams, but if you edit the dream now they all get updated to the new one. Thel fucked around with this message at 03:57 on Dec 30, 2010 |
# ? Dec 30, 2010 03:54 |
|
e: n/m, contradictory data.
McGlockenshire fucked around with this message at 07:51 on Dec 30, 2010 |
# ? Dec 30, 2010 07:47 |
|
I'm trying to get specific values out of a SQL field which contains XML. The problem is, no matter what I do I keep getting nothing but NULL. I can't for the life of me figure out what sort of mystic sorcery I'm supposed to use to get the value. Basically, the value SHOULD be three levels down, but I only get nulls back. I have found one thing, though. If I take the XML field and declare it as a variable, I can SOMETIMES get a value back. But if it has a property in the tag, it stops working. Here's what I have that will work: declare @XML XML set @XML = '<ApplicationInfo> <ApplicationId>0</ApplicationId> </ApplicationInfo>' SELECT @XML.value('/ApplicationInfo[1]/ApplicationId[1]', 'nvarchar(max)') as FirstName That gives me an actual value back. declare @XML XML set @XML = '<ApplicationInfo xmlns="http://sungardhe.com/PowerCAMPUS/Application/"> <ApplicationId>0</ApplicationId> </ApplicationInfo>' SELECT @XML.value('/ApplicationInfo[1]/ApplicationId[1]', 'nvarchar(max)') as FirstName That gives me NULL. How do I make it ignore the xmlns? Again, though, this is what I've been able to cobble together. The actual thing I want to get back is within a field of a table, not in a variable. Is there an easier way to get a value back from XML in a field?
|
# ? Jan 4, 2011 15:56 |
|
Are you doing something with PowerCampus's Self-Service product? PM me with more details and I can try it on a test box at my school.
|
# ? Jan 4, 2011 21:32 |
|
slartibartfast posted:Are you doing something with PowerCampus's Self-Service product? PM me with more details and I can try it on a test box at my school. I am, but the thing I'm needing to do has nothing to do with the product at all. I'm just trying to get something out of a field populated with XML. EDIT: As an additional bit of info, I've found that the existence of "xmlns" as an attribute screws up any attempt to use XML queries. Is there a reason for this, or a way around it? Akrabbim fucked around with this message at 21:49 on Jan 4, 2011 |
# ? Jan 4, 2011 21:47 |
|
Akrabbim posted:I am, but the thing I'm needing to do has nothing to do with the product at all. I'm just trying to get something out of a field populated with XML. xmlns is a namespace declaration and declaring it in the way you are doing specifies it as the default namespace, which is why you are getting nulls, as you're querying the 'global' namespace and your fields are hidden away in the "http://sungardhe.com/PowerCAMPUS/Application/" namespace. Pretty sure thats what it's doing, as if you use /*[1]/*[1] (wildcards) you'll get the right results, which implies that they are there, they just aren't called what you think they are. I did some googling and couldn't see a way of specifying an XPath statement to execute within a specified namespace. There's probably some simple way of doing it but I don't know what it is, sorry. EDIT: Akrabbim posted:The actual thing I want to get back is within a field of a table, not in a variable. Is there an easier way to get a value back from XML in a field? code:
Primpod fucked around with this message at 23:08 on Jan 4, 2011 |
# ? Jan 4, 2011 22:54 |
|
That appeared to do it. I got it to work in the actual query, but it's still kinda annoying that that happens. Is there any way you know of to figure what name SQL is giving to the fields?
|
# ? Jan 4, 2011 23:17 |
|
I've heard of this as a new interview question, has anyone else? When shouldn't you use SQL? Are they talking specifically when not to use a certain version, or in general, like don't use a database instead of XML/spreadsheet/zip file to store stuff.
|
# ? Jan 6, 2011 22:25 |
|
Bob Morales posted:I've heard of this as a new interview question, has anyone else? There's two cases I can think of from a general perspective: 1) You can do a lot of processing in SQL, but there's diminishing returns, and so know when to pass the results to/from scripting. 2) For extremely large graph datasets, there are better DBMS options, such as Neo4J, that are optimized for graph queries.
|
# ? Jan 6, 2011 22:30 |
|
I guess theoretically it could also refer to data that simply can't be normalized or doesn't make sense in a relational database but most industries that require that kind of thing have their own solutions.
|
# ? Jan 6, 2011 23:13 |
|
They could also use "shouldn't use SQL" to mean "don't need ACID," referring to one of the new shard-based/distributed "NoSQL" key-value stores that sacrifice one or more of the ACID principals in the name of performance.
|
# ? Jan 7, 2011 00:49 |
|
McGlockenshire posted:They could also use "shouldn't use SQL" to mean "don't need ACID," referring to one of the new shard-based/distributed "NoSQL" key-value stores that sacrifice one or more of the ACID principals in the name of performance. Yeah, that would be a smart answer. I think the question has a few such smart answers, and it's probably also going to tell them what part of the data ecosystem you consider yourself to live in.
|
# ? Jan 7, 2011 01:21 |
|
Ok I think I know the answer to my question is that Access is a piece of poo poo but just to be sure: I have a query that's basically: SELECT 'a bunch of random poo poo' from table_a, table_b WHERE left(table_a.sgl_account, 4) = table_b.sgl This query runs fine. I then try: SELECT 'a bunch of random poo poo' from table_a, table_b WHERE left(table_a.sgl_account, 4) = left(table_b.sgl, 4) This causes Access to poo poo itself and freeze when running the query. As a bonus, the following query works perfectly fine too: SELECT 'a bunch of random poo poo' from table_a, table_b WHERE left(table_a.sgl_account, 4) = sgl and left(table_a.sgl_account, 4) = left(sgl, 4) So am I retarded in not understanding why this happens or is Access retarded? I'm a bit frustrated at the moment if you can't tell. I feel like I'm trying random voodoo at thist point in trying to get this thing to work.
|
# ? Jan 7, 2011 20:20 |
|
bamhand posted:So am I retarded in not understanding why this happens or is Access retarded? Access can be so absurdly bad that even SQLServer has support articles bemoaning it. I don't have any particular experience with what you're dealing with, except to say that when I was forced to work on Access for a week last year, I found any complex queries were best built with their crappy query construction tool and not by writing the SQL by hand. I don't know if that helps.
|
# ? Jan 7, 2011 20:35 |
|
The query is built by a vb script based off some choices that you make on a menu. I also have no idea how the query builder works. *edit* I fixed the issue by just adding another column to table_b that was equal to left(sgl, 4) and now everything is fine and dandy. If anyone has insight on why the previous query doesn't work I would still appreciate it. My guess is that for the third query once it runs through that first condition the second condition using left(sgl_account, 4) = left(sgl, 4) doesn't have many comparisons to do? I really no nothing about efficiency when it comes to coding, all my SQL I pretty much learned on the job with a lot of googling. bamhand fucked around with this message at 20:46 on Jan 7, 2011 |
# ? Jan 7, 2011 20:40 |
|
bamhand posted:The query is built by a vb script based off some choices that you make on a menu. I also have no idea how the query builder works.
|
# ? Jan 7, 2011 21:11 |
|
Ugh, I don't know why I have such an absurdly hard time wrapping my head around PIVOT/UNPIVOT, but before I bang my head bloody against my desk anymore, I figured I'd ask for help. I have a table with a customer ID and a bunch of columns with rules, so it looks like this: code:
code:
Now, I think the solution is to somehow unpivot the first table with the CatCode in 1 column and the bit value in another, but I'm not seeing a way to do it. This is part of a development project and it's in the beginning stages so I can re-do the tables if necessary, I just figured it'd be better this way instead of a row for each value.
|
# ? Jan 10, 2011 17:51 |
|
You really should change the first table so that you can join without worrying about the pivot/unpivot. There are many reasons, but one big one is that you don't want values as part of your column headers in case that changes. I'd probably change the first table to be something like: code:
|
# ? Jan 11, 2011 02:05 |
|
Aredna posted:You really should change the first table so that you can join without worrying about the pivot/unpivot. There are many reasons, but one big one is that you don't want values as part of your column headers in case that changes. Yeah, I'll probably end up doing this, I was just hoping to avoid it. The other way just seemed more efficient to me for some reason.
|
# ? Jan 11, 2011 06:53 |
|
I've got a table with current inventory levels by dimension and no history tracking other than summing up everything in a transaction table. I'd like to start keeping a daily snapshot of this table and have my own database on the server (SQL Server 2008) where I can store the data. The current table has ~1.2m records, however only ~45k records have a non-zero on hand value and only ~10k records will change on any given day. Rather than store the entire table each day I'm thinking of storing a full snapshot on the first day and then each day only storing records that have changed. Here's my current update query from testing that should give an idea of how I'm currently going about it. The table is unique on (sku, dimension) code:
My update script takes about 7s currently to run and I have to wonder if that will get out of hand in a year so any suggestions for a better way to do this are appreciated.
|
# ? Jan 12, 2011 00:41 |
|
Have you considered adding triggers on the inventory table instead? You can use this to capture before-and-after data into another table, which may prove more useful than a daily snapshot. You can even build the daily snapshot from that data by effectively re-playing the log. (Any sane inventory system should already be doing this, either at the table level or at the application level...)
|
# ? Jan 12, 2011 07:25 |
|
McGlockenshire posted:Have you considered adding triggers on the inventory table instead? You can use this to capture before-and-after data into another table, which may prove more useful than a daily snapshot. You can even build the daily snapshot from that data by effectively re-playing the log. This man speaks truth, trigger populated history tables are immensely useful.
|
# ? Jan 15, 2011 07:34 |
|
If I have an excel spreadsheet that I need to have automatically imported every night into SQL Server 2005 by a scheduled task, what would be the best way to achieve this?
|
# ? Jan 16, 2011 04:59 |
|
|
# ? Jun 8, 2024 08:15 |
|
three posted:If I have an excel spreadsheet that I need to have automatically imported every night into SQL Server 2005 by a scheduled task, what would be the best way to achieve this? Do you have access to SQL Server Integration Services? If so, that's a very easy thing to pull off with SSIS. You'll need SSIS installed on the server, and access to a machine with the Business Intelligence Development Studio (BIDS) installed. This can be found as part of the Management tools on the SQL Server install. You'll use BIDS to create the SSIS package, and then create a maintenance task or SQL Agent job on the target server that can fire the SSIS package on a specified schedule. In SSIS, it's just a Data Flow task: Excel as the source, and SQL Server as the destination. You can find plenty of tutorials online for how to do a basic Data Flow task in SSIS 2005. If SSIS isn't an option, you could probably write a VBScript or a PowerShell script that could do this, and then schedule that via the Scheduled Tasks in the Control Panel.
|
# ? Jan 16, 2011 20:27 |